How to make a choice in Select

Question

I have a table containing a unique ID field. Another field (REF) contains a link to another data set ID field. Now I need to select all datasets where REF points to a dataset that does not exist.

SELECT * FROM table WHERE ("no dataset with ID=REF exists") 

How can i do this?

Answer 1

3 ways

 SELECT * FROM YourTable y WHERE NOT EXISTS (SELECT * FROM OtherTable o WHERE y.Ref = o.Ref) SELECT * FROM YourTable WHERE Ref NOT IN (SELECT Ref FROM OtherTable WHERE Ref IS NOT NULL) SELECT y.* FROM YourTable y LEFT OUTER JOIN OtherTable o ON y.Ref = o.Ref WHERE o.Ref IS NULL 

See also Five ways to return all rows from one table that are not in another table

Answer 2

Try the following:

 SELECT * FROM TABLE WHERE NOT EXISTS (SELECT * FROM OtherTable WHERE TABLE.Ref = OtherTable.ID) 

Answer 3

I think this should work

 SELECT * FROM table WHERE id NOT IN (SELECT ref_id FROM ref_table) 

or using join

 SELECT table.* FROM table LEFT JOIN ref_table ON table.id = ref_table.ref_id WHERE ref_table.ref_id IS NULL 

Answer 4

 SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id = table2.ref WHERE table2.ref IS NULL 

Answer 5

You can execute a subquery, for example:

 select * from table where somefield not in (select otherfield from sometable where ID=REF) 

Answer 6

 SELECT * FROM table WHERE ((SELECT COUNT(*) FROM table2 WHERE table2.id = table.ref) = 0) 

Answer 7

Something like that:

 SELECT * FROM table WHERE ID NOT IN(SELECT REF FROM Table2 ) 

Answer 8

yo dawg i herd u liek selects, so we put select in ur select so you can request the request during the request.

Answer 9

Yes you can use

select * from x where does not exist (select * from y)