negative selection SQL Query

Question

I have been tasked with returning a negative selection from our sql database. I'll define the criteria as best i can. Thus far I haven't crafted a query that has worked.

Business Table

[Bus Name] [Bus ID]

Activity Table

[Activity ID] [Bus ID]

Activity Extension Table

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

I need the Business names for all businesses that do not have a record with that businesses id # in the associated tables. Simply put, all businesses without activities. The Business ID can be present in one or both of the associated tables.

This has caused me trouble for a few hours while trying to craft queries with joins and not exists or not in statements. No success.

Any ideas?

Thanks D

Answer 1

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

Answer 2

I would use a combination of a left join and a union, 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 

Answer 3

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

---

Because the relationship is a foreign key (business_id), it's safe to assume none of them to be null. In which case, NOT IN and NOT EXISTS are the best means of looking for missing values in SQL Server. LEFT JOIN/IS NULL is less efficient - you can read more about it in this article.