CODEWITHSUNDEEP
sqliteselect
yaylitzis
yaylitzis

Reputation: 5544

Select the records which weren't selected from a SELECT

I select all the records from a table LABELS, I and I use also INNER JOIN to get with my SELECT some other data which exist in other tables. So my query is:

SELECT LABELS.oldLabel, LABELS.label, LABELS.version, LABELS.description, LABELS.type, LABELS.cutter, LABELS.valid, LABELS.dateCreated, LABELS.dateModified, CUSTOMERS.name AS customer, CUSTOMERS.id, SUPPLIERS.name AS supplier FROM LABELS 
INNER JOIN CUSTOMERS 
ON LABELS.customer = CUSTOMERS.id 
INNER JOIN SUPPLIERS 
ON LABELS.supplier = SUPPLIERS.id 
GROUP BY LABELS.oldLabel, LABELS.label, LABELS.version, LABELS.description, LABELS.type, LABELS.cutter, LABELS.customer, LABELS.valid, CUSTOMERS.name , CUSTOMERS.id, SUPPLIERS.name

The size of the records in the table LABELS is 3169. However, the above statement returns 3089. 80 records are missing.

Is there a way to get all the records, except from the above select in order to see which are these records and figure out why they are not selected ?

I tried:

Select * FROM labels where not exists( the_above_select)

But I don't get any records..

Upvotes: 0

Views: 22

Answers (1)

apomene
apomene

Reputation: 14389

SELECT * from Labels WHERE label NOT IN 
(SELECT  label from Labels INNER JOIN CUSTOMERS
   ON LABELS.customer = CUSTOMERS.id 
   INNER JOIN SUPPLIERS 
   ON LABELS.supplier = SUPPLIERS.id)

Upvotes: 1

Related Questions