SQL statement to find what's missing...

Question

Post reply

SQL statement to find what's missing...

Rob Fisch

Valued Member

Points: 70
More actions
March 4, 2010 at 6:40 pm

#216306

This might seem like a simple question, but the answer has me stumped. What is a SQL statement that can be used to find a list names of people that do not have apples in this dataset? (Obviously, John and Jim will not appear in the results.)
Name Fruit Qty
John Apple3
John Banana 2
John Pear 4
Jim Apple 3
Jim Pear 2
Ed Banana 6
Ed Pear 8
Sally Banana 2
Sally Pear 9

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

SELECTDISTINCT [Name]

FROMTableName

WHERE[Name] NOT IN ( SELECT DISTINCT [Name] FROM TableName WHERE Fruit = 'Apple' )

I hope the Column Name doesnot contain NULL values

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

SELECT DISTINCT [Name]
FROM TableName
WHERE Fruit <> 'Apple'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Kingstons response is more accurate for your desired results.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 4

Please explain why you would recommend a nested select on the same table, looking for NOT IN on the same column, instead of selecting it and excluding the undesired data?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

Henrico Bekker (3/4/2010)
Please explain why you would recommend a nested select on the same table, looking for NOT IN on the same column, instead of selecting it and excluding the undesired data?

Because it doesn't return the desired results. It returns John and Jim also..

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Henrico Bekker (3/4/2010)
Please explain why you would recommend a nested select on the same table, looking for NOT IN on the same column, instead of selecting it and excluding the undesired data?

Your response is fine for excluding the 'Apples'

The OP also wanted to exclude anybody who had apples (even if they have different fruit as well).

Thus Kingston's response was more accurate. Had I not re-read the requirements, I would have written a query similar to what you did - more straight forward.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 7

Noted, my apologies, didnt read the question thoroughly.

🙂

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Henrico Bekker (3/4/2010)
Noted, my apologies, didnt read the question thoroughly.
🙂

NP

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Garadin One Orange Chip Points: 29613 More actions · Answer 9

Something to keep in mind when using NOT IN:

http://www.sqlservercentral.com/blogs/never_say_never/archive/2010/01/28/in-and-not-in.aspx

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

This will eliminate the "NOT IN"

with apples as (Select [name] from tablename where fruit = 'Apple')

Select distinct t.Name

From TableName T

Left Outer Join apples a

On t.name = a.name

Where a.name is null

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Rob Fisch Valued Member Points: 70 More actions · Answer 11

Rob Fisch

Valued Member

Points: 70

March 5, 2010 at 8:23 am

#1129204

Thanks, everyone.

Rob

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

you're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Henrico Bekker (3/4/2010)
SELECT DISTINCT [Name]
FROM TableName
WHERE Fruit <> 'Apple'

I haven't tried it but I believe that will still return "John".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 14

Simple:

SELECT name FROM @data EXCEPT

SELECT name FROM @data WHERE fruit = 'Apple';

:w00t:

Full test rig

DECLARE @data

TABLE (

name VARCHAR(50) NOT NULL,

fruit VARCHAR(30) NOT NULL,

quantity INTEGER NOT NULL

);

INSERT @data (name, fruit, quantity)

SELECT 'John', 'Apple', 3 UNION ALL

SELECT 'John', 'Banana', 2 UNION ALL

SELECT 'John', 'Pear', 4 UNION ALL

SELECT 'Jim', 'Apple', 3 UNION ALL

SELECT 'Jim', 'Pear', 2 UNION ALL

SELECT 'Ed', 'Banana', 6 UNION ALL

SELECT 'Ed', 'Pear', 8 UNION ALL

SELECT 'Sally', 'Banana', 2 UNION ALL

SELECT 'Sally', 'Pear', 9

SELECT name FROM @data EXCEPT

SELECT name FROM @data WHERE fruit = 'Apple';

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi