Help - find values in table 1 NOT in table2

Question

Post reply

Help - find values in table 1 NOT in table2

ibanez59

Newbie

Points: 8
More actions
October 15, 2010 at 1:50 pm

#227974

Please help. I'm a newbie at this and I know this should be easy (and probably is).
I have two tables. Both tables contain a common field (column) name. I need to get a list of values that appear in this column in table 1 that do NOT appear in the column in table 2.
So if I have this in table 1:
Printer
------------
Prt1
Prt2
Prt3
Prt4
and have this is table 2:
Printer
-------------
Prt1
Prt2
I need help with a query that will get me this:
Prt3
Prt4
I'm sure for some of you folks with a lot of experience this probably a no-brainer, but (like I said) I'm a newbie and trying to learn as much as I can as I wade through all this.
Please Help!

Viewing 8 posts - 1 through 8 (of 8 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

SELECT Printer

FROM Table1

EXCEPT

SELECT Printer

FROM Table2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Or, for the [EDIT] eternally stuck in 2k mindset... [/EDIT], a bit more oldschool:

SELECT

t1.*

FROM

Table1 AS t1

LEFT JOIN

Table2 AS t2

ON t1.Printer = t2.Printer

WHERE

t2.Printer IS NULL

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95461 More actions · Answer 3

Craig Farrell (10/15/2010)
Or, for the 2k8 deprived,

Umm, Craig, this is valid syntax for 2005 also! :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

WayneS (10/15/2010)
Craig Farrell (10/15/2010)
Or, for the 2k8 deprived,
Umm, Craig, this is valid syntax for 2005 also! :w00t:

I think I'm going to go hide in my corner now. I'll do less damage there... :blush:

This has not been a good week for me on SSC....

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95461 More actions · Answer 5

Craig Farrell (10/15/2010)
WayneS (10/15/2010)
Craig Farrell (10/15/2010)
Or, for the 2k8 deprived,
Umm, Craig, this is valid syntax for 2005 also! :w00t:
I think I'm going to go hide in my corner now. I'll do less damage there... :blush:
This has not been a good week for me on SSC....

LOL! :w00t:

(Think of how much that you learned! Sounds like it's been a good week for learning!)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

WayneS (10/15/2010)
LOL! :w00t:
(Think of how much that you learned! Sounds like it's been a good week for learning!)

Oh learned plenty so far. My SQL learning is simply matched up with the same amount of learning of how good my feet taste with different garnishments.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

ibanez59 Newbie Points: 8 More actions · Answer 7

ibanez59

Newbie

Points: 8

October 19, 2010 at 7:17 am

#1239018

Thanks a bunch to everyone that replied!