Negative SQL query selection

Question

I was instructed to return a negative selection from our sql database. I will determine the criteria as best as possible. So far I have not created a query that worked.

Business table

[Bus Name] [Bus Identifier]

Action table

[Activity ID] [Bus ID]

Activity Extensions Table

[Ext ID] [Activity ID] [Bus ID]

I need company names for all enterprises that do not have an entry with the company identifier # in the linked tables. Simply put, all businesses are idle. A business identifier may appear in one or both related tables.

This caused me problems for several hours trying to process requests using connections and does not exist or not in the statements. No success.

Any ideas?

Answer 1

Using NOT IN

---

 SELECT b.* FROM BUSINESS b WHERE b.business_id NOT IN (SELECT a.business_id FROM ACTIVITY a) AND b.business_id NOT IN (SELECT ae.business_id FROM ACTIVITY_EXTENSION ae) 

Using NOT EXISTS

---

 SELECT b.* FROM BUSINESS b WHERE NOT EXISTS (SELECT NULL FROM ACTIVITY a WHERE a.business_id = b.business_id) AND NOT EXISTS (SELECT NULL FROM ACTIVITY_EXTENSION ae WHERE ae.business_id = b.business_id) 

Using LEFT JOIN/IS NULL

---

  SELECT b.* FROM BUSINESS b LEFT JOIN ACTIVITY a ON a.business_id = b.business_id LEFT JOIN ACTIVITY_EXTENSION ae ON ae.business_id = b.business_id WHERE a.business_id IS NULL AND ae.business_id IS NULL 

Conclusion

---

Since the relation is a foreign key (business_id), it is safe to assume that none of them is null. In this case, NOT IN and NOT EXISTS are the best way to find missing values ​​in SQL Server. LEFT JOIN / IS NULL is less efficient - you can read about it in this article .

Answer 2

SELECT * FROM businesses WHERE business.id NOT IN (SELECT DISTINCT business_id FROM activities)

Answer 3

I would use a combination of left join and join, something like:

 select * from Business b left join (select id, bid from Activity union select id, bid from ActivityExtension) a on b.id=a.bid where a.bid=null