如果左联接表记录计数大于1，则排除行。

Two tables:

两个表:

Table1 fax_history

表1 fax_history

fax_key
1001
1002

Table2 > fax_history_status

表二> fax_history_status

fax_key     Status
1001        NEW
1001        SUCCESS
1002        NEW

Now I need to write a join query which will return only fax_key=1002 record because fax_key=1001 has MORE THAN ONE record in fax_history_status table.

现在我需要编写一个连接查询，它只返回fax_key=1002条记录，因为fax_key=1001在fax_history_status表中有多条记录。

So the query result should be:

因此查询结果应该是:

fax_key     status
1002        NEW

2 个解决方案

#1

you could filter the rows using having

您可以使用have对行进行筛选

select a.fax_key 
from  fax_history a
inner  join  fax_history_status  b on  a.fax_key  = b.fax_key 
 group by a.fax_key
 having count(*) =1

for status you could use a (fake) aggregation function eg:

对于状态，可以使用(伪)聚合函数，如:

select a.fax_key , min(b.status)
from  fax_history a
inner  join  fax_history_status  b on  a.fax_key  = b.fax_key 
 group by a.fax_key
 having count(*) =1

#2

you could use the basic query like that

您可以使用这样的基本查询

SELECT * FROM fax 

INNER JOIN faxstatus ON fax.faxkey=faxstatus.faxkey AND faxstatus.faxkey IN

(

SELECT faxkey  FROM faxstatus

GROUP BY faxkey

HAVING COUNT(faxkey)=1


)

#1