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

Two tables:

Table1 fax_history

  • SQL Server Reporting Services for Amazon RDS
  • Extracting a (sampled) time series from an SQL DB
  • Select into statement where source is other database
  • SQL Server : query correlation from two tables
  • Saving changes after table edit in SQL Server Management Studio
  • Executing an Oracle Stored Procedure from SQL Server 2005
  • 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
    

  • When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  • Why does my DB project's .dbmdl file change even when I make no changes to the project?
  • Clustered indexes on non-identity columns to speed up bulk inserts?
  • Arranging Rows with Similar Column Data Together After Using ORDER BY
  • How to transfer ASP.NET MVC Database from LocalDb to SQL Server?
  • How do I enforce data integrity rules in my 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.