|
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) |
