it seems like I've accomplished this before, but struggling again. Here is my data:
tblHotlist
----------
ID
hotlistStatus
buildNumber
loadType
etc
tblessr
-------
ID
esHeadline
notesStatus
actionItems
bugStatusID
etc
tblBugStatus (not needed in query)
------------
ID
bugStatus
etc
tbl_j_hlbug
-----------
esID
hotlistID
timestamp
I want all the records from tblHotlist and if records exist in tblESSR, I need those where bugStatusID=300. I've tried several different joins and subqueries, but still can't get the results I need. Once I put the qualifier of bugStatusID=300, I only get the records from tblHotlist where records from tblESSR has a bugStatusID of 300.
failed attempt:
SELECT hl.hotlistID, hl.buildnumber, es.ID, es.notesStatus, es.actionItems
FROM tblhotList hl
LEFT OUTER JOIN tbl_j_hlbug j ON j.hotlistID = hl.id
LEFT OUTER JOIN tblESSR es ON j.esrID = es.id
WHERE hl.hotlistStatusID=100 AND hl.loadType='su' AND es.bugStatusID=300
Any help would be appreciated. I've tried different joins and a couple of subqueries, but I always get the same result.
Thanks!
You either have to move the limiting criteria to the joins or also look for null values.
WHERE hl.hotlistStatusID=100 AND hl.loadType='su' AND
(es.bugStatusID=300 or es.bugStatusID is null)
When the outer joins occur, you have to consider null values will exist on records that don't have matching data. As such if you try to limit by these, you will end up excluding the nulls w/o matching data; thereby negating the outer join. Sometimes this is what you want... sometimes it isn't. In this case I think you wanted the nulls and 300.
SELECT hl.hotlistID, hl.buildnumber, es.ID, es.notesStatus, es.actionItems
FROM tblhotList hl
LEFT OUTER JOIN tbl_j_hlbug j ON j.hotlistID = hl.id
LEFT OUTER JOIN tblESSR es
ON j.esrID = es.id
AND es.bugStatusID=300
WHERE hl.hotlistStatusID=100 AND hl.loadType='su'
the Hl where clause doesn't matter as you're getting all records to begin with.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments