January 7, 2008 at 11:02 am
The query below is giving me duplicate results. But I can't think of another way to get the results I need.
The batchID is the ID in the cbatch table that I am trying to match. This ID is referenced in the slist table. However, it is NOT referenced in the elist table.
So I have to use the moduleID that is in the slist and elist table to join them.
Is there a better way to write this?
SELECT cb.batchid,
sl.moduleID,
el.elementID,
el.pageID
FROM slist AS sl
LEFT JOIN cbatch AS cb ON cb.ModuleID = sl.ModuleID
LEFT JOIN elist AS el ON el.moduleID = sl.ModuleID
WHERE cb.BatchID = '11122007113953'
Thanks!
January 7, 2008 at 11:23 am
Does "select distinct" solve what you're running into?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2008 at 11:34 am
Depending on the number of matches you may need to use DISTINCT or GROUP BY to eliminate the duplicates becuase if a join enforces more than one row between CB/SL/EL in any combination you may only think it is duplicated based on what you want to know.
SELECT DISTINCT cb.batchid,
sl.moduleID,
el.elementID,
el.pageID
FROM slist AS sl
LEFT JOIN cbatch AS cb ON cb.ModuleID = sl.ModuleID
LEFT JOIN elist AS el ON el.moduleID = sl.ModuleID
WHERE cb.BatchID = '11122007113953'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply