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

Two tables:

Table1 fax_history

  • Getting null value from Attendance Log table and Employee table using SQL Server query?
  • update sql server datetime field
  • SQL Server understand SCOPE_IDENTITY()
  • Sum days between differing date/times progromatically
  • replace only matches the beginning of the string
  • Require dropdown selection with database populated items
  • 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
    

  • How to model User and Profiles scenario
  • Create DB in Sql Server based on Visio Data Model
  • What would be the best table structure?
  • Why database designers do not make IDENTITY columns start from the min value rather than 1?
  • LINQ to Entities timeout on SubmitChanges()
  • Getting ranges that are not in database
  • 2 Solutions collect form web for “Exclude rows if LEFT JOIN table record count is more than one”

    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 
    

    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
    
    
    )
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.