根据另一个表上的条件在一个MySQL表中查找条目

I've got a table of hardware and a table of incidents. Each hardware has a unique tag, and the incidents are tied to the tag.

我有一张硬件表和事件表。每个硬件都有一个唯一标记,事件与标记相关联。

How can I select all the hardware which has at least one incident listed as unresolved?

如何选择至少有一个事件列为未解决的所有硬件?

I can't just do a join, because then if one piece of hardware had multiple unresolved issues, it would show up multiple times.

我不能只是做一个连接,因为如果一个硬件有多个未解决的问题,它会出现多次。

3 个解决方案

#1

select distinct(hardware_name) 
from hardware,incidents 
where hardware.id = incidents.hardware_id and incidents.resolved=0;

#2

Something like this should do it:

这样的事情应该这样做:

Select A.HardwareID A.HadwareName, B.UnresolvedCount
From (Hardware A) 
Inner Join 
(
  Select HardwareID, Count(1) As UnresolvedCount 
  From Incidents 
  Where Resolved = 0 
  Group By HardwareID
) As B On A.HardwareID = B.HardwareID

#3

This can also work

这也可以

SELECT hd.name, inc.issue, FROM hardware hd INNER JOIN inc ON hd.tag = inc.tag AND inc.issue = 'unresolved' group by hd.name

#1

select distinct(hardware_name) 
from hardware,incidents 
where hardware.id = incidents.hardware_id and incidents.resolved=0;

#2