CODEWITHSUNDEEP
sql-serversql-server-2008
Patrick
Patrick

Reputation: 2577

Using except to find certain rows in sql

I'm trying to find rows from one table where they don't have a corresponding record in another table. 

 SELECT CUST_NUMBER,
             REFNUMBER,
             'Application No Longer Exists; ' as Exception
              from Data
              except 
              select CUST_NUMBER
              from applications

The problem is SQL doesn't like mismatched column counts. How can I get the missing rows and associated information?

Upvotes: 0

Views: 29

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

select 
* from data t1
where not exists(select 1 from applications a where a.cust_number=t1.cust_number)


;With cte
as
(
select cust_number from data
except 
select cust_number applications 
)
select * from data where cust_number in (select cust_number from cte)

Upvotes: 1

Related Questions