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)
