Oops, except you need GROUP BY commands in the 3rd and 4th selects. Bill
On Tue, Nov 17, 2009 at 2:20 PM, Bill Downall < [email protected]> wrote: > 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) >> >> >
