如何从一个表中获取所有记录，仅从连接表中获取带有条件的记录

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.

我想要来自tblHotlist的所有记录,如果记录存在于tblESSR中,我需要那些bugStatusID = 300的记录。我尝试了几种不同的连接和子查询,但仍然无法获得我需要的结果。一旦我把bugStatusID = 300的限定符,我只从tblHotlist获取记录,其中来自tblESSR的记录的bugStatusID为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!

2 个解决方案

#1

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.

当外连接发生时,您必须考虑在没有匹配数据的记录上将存在空值。因此,如果您尝试通过这些限制,最终将排除没有匹配数据的空值;从而否定了外连接。有时这就是你想要的......有时它不是。在这种情况下,我认为你想要nulls和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.

当你获得所有记录时,Hl where子句无关紧要。

#2

Because of the join, some of your result set BEFORE the WHERE will have NULL in bugStatusID, so you need to add this into your where if you wish to see those results as well.

由于连接,在WHERE之前的一些结果集在bugStatusID中将为NULL,因此如果您希望看到这些结果,则需要将其添加到您的位置。

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  OR es.bugStatusID IS NULL)

#1