What Dennis said, or do this: SELECT T1.grpid, T1.name, COUNT (DISTINCT T2.colid), COUNT (T3.itemid) FROM groups T1, collections T2, items T3 WHERE T1.grpid = T2.grpid AND T2.colid = T3.colid GROUP BY T1.grpid, T1.name UNION ALL SELECT t4.grpid, t4.name, (0), (0) FROM groups t4 WHERE t4.grpid NOT IN (select grpID from Collections) AND t4.colid NOT IN (select colid FROM items) UNION ALL SELECT t5.grpid, t5.name, count (distinct T2.colid), 0 FROM Groups t5, collections t6 WHERE t5.grpid = t6.grpid AND t5.colID NOT IN (select colid FROM items) UNION ALL SELECT t7.grpid, t7.name, (0), count (t8.itemid) FROM groups t7, items t8 WHERE t7.colid = t8.colid AND t7.grpid NOT IN (select grpid FROM Collections)
On Tue, Nov 17, 2009 at 2:01 PM, Jason Kramer <[email protected]> wrote: > I have three tables that I am trying to draw information from with an > OUTER JOIN. I need to use an OUTER JOIN because I need to see all rows from > the first table, even if there are no corresponding entries in the second > and third table. If I issued the command: > > SELECT T1.grpid, T1.name, COUNT (DISTINCT T2.colid), COUNT T3.itemid FROM > groups T1, collections T2, items T3 WHERE T1.grpid = T2.grpid AND T2.colid = > T3.colid GROUP BY T1.grpid, T1.name > > I get: > 0 Group 0 10 38 > 2 Group 2 22 450 > etc... > > There is a Group 1, but it has no collections, and therefor no items. A > group can have 0 to many collections, a collection can have 1 to many > items. Each item must belong to one collection, and each group must belong > to one collection. I want to see a print out like: > 0 Group 0 10 38 > 1 Group 1 0 0 > 2 Group 2 22 450 > > I tried: > SELECT T1.gprid, T1.name, COUNT (DISTINCT T2.colid), COUNT T3.itemid FROM > groups T1 LEFT OUTER JOIN collections T2 ON T1.grpid = T2.grpid, LEFT OUTER > JOIN items T3 ON T2.colid = T3.colid GROUP BY T1.grpid, T1.name > but I get error: Syntax is incorrect for the command SELECT (2045). > > If I enter: > SELECT T1.gprid, T1.name, COUNT (DISTINCT T2.colid) FROM groups T1 LEFT > OUTER JOIN collections T2 ON T1.grpid = T2.grpid GROUP BY T1.grpid, T1.name > I get: > 0 Group 0 10 > 1 Group 1 0 > 2 Group 2 22 > > Can I use multiple OUTER JOIN statements? If not, is there a way to get > what I want without creating a temporary table? > > Thanks, > Jason > > -- > Jason Kramer > University Archives and Records Management > 002 Pearson Hall > (302) 831 - 3127 (voice) > (302) 831 - 6903 (fax) > >
