CODEWITHSUNDEEP
sqlsql-serverdatabasejoinleft-join
ray
ray

Reputation: 4250

Exclude rows if LEFT JOIN table record count is more than one

Two tables:

Table1 fax_history

fax_key
1001
1002

Table2 > 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.

So the query result should be:

fax_key     status
1002        NEW

Upvotes: 1

Views: 334

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

you could filter the rows using having 

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

Upvotes: 2

pankaj kumar singh
pankaj kumar singh

Reputation: 17

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


)

Upvotes: 0

Related Questions