Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

negative selection SQL Query

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?

like image 576
Digital ink Avatar asked Jun 23 '10 23:06

Digital ink


People also ask

How do I get negative values in SQL?

In SQL Server, the T-SQL SIGN() function returns the sign of a number. In other words, it indicates whether or not the value is a positive number, a negative number, or zero.

What is MINUS query in SQL?

What is a Minus Query? A Minus Query is a query that uses the MINUS operator in SQL to subtract one result set from another result set to evaluate the result set difference. If there is no difference, there is no remaining result set. If there is a difference, the resulting rows will be displayed.

How do you exclude something in SQL query?

To exclude multiple values to be fetched from a table we can use multiple OR statements but when we want to exclude a lot of values it becomes lengthy to write multiple AND statements, To avoid this we can use the NOT IN clause with the array of values that need to be excluded with the WHERE statement.

What is MINUS operator in SQL with example?

The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query.


2 Answers

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.

like image 93
OMG Ponies Avatar answered Oct 23 '22 08:10

OMG Ponies


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

like image 34
Jamie Wong Avatar answered Oct 23 '22 07:10

Jamie Wong