select rows not in another table?

Question

How do I select rows in one table while not in another table? The two table share several columns with same data type and same meaning. 

The following SQL is not correct but can interpret what I mean. 

select pay_date,pay_store,pay_invoice 
from pay where (pay_date,pay_store,pay_invoice) 
not in (select u_date,u_store,u_inv_num from sales)

Anonymous · Accepted Answer

Already on the right track here, so I will just add some clarification. Also note that I'm using MySQL here and avoiding nested select statements for compatibility with older versions.

Sometimes it's best to construct a query a small amount at a time so that you can see the effect. To keep the samples concise I will check on one field only. First, check the different results for the following....

SELECT 
pay_date, 
u_date,
FROM 
sales, pay
WHERE 
pay_date = u_date


Now compare this with...

SELECT 
pay_date, 
u_date,
FROM 
sales
LEFT JOIN pay
ON pay.pay_date = sales.u_date


Notice how in the left join, the rows that fail the match still appear, but with NULL as the field? That's where the answer lies...


SELECT 
pay_date, 
u_date,
FROM 
sales
LEFT JOIN pay
ON pay.pay_date = sales.u_date
WHERE pay.pay_date IS NULL


When a value has the potential of not being unique, you can test for it's existence or lack thereof with something like this:

SELECT 
sales.u_store,
IF(COUNT(pay.pay_date) > 0,'Yes','No') as 'Date Exists'
FROM 
sales
LEFT JOIN pay
ON pay.pay_date = sales.u_date
GROUP BY sales.u_store

ramesh · Answer

Use some key fields in Select and use NOT IN or NOT EXISTS in the other table. Hope this should workout.

sinkablehail1978 · Answer

try this:

Select p.paydate, p.pay_store, p.pay_invoice
From pay p
left outer join sales s
on
p.paydate = s.u_date
and
p.pay_store = s.u_store
and
p.invoice = s.u_inv_num
where
s.paydate is null

Trending News

select rows not in another table?

3 Answers