Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ddouma
Starting Member
10 Posts |
 Posted - 2007-11-08 : 10:32:05
|
| Probably an easy one, but the solution escapes me. I want to show all records that aren't found in the IN statement. So using the example table and SQL statement below, I would like to get back 'Mark' and 'Ted' as non-matching items in my IN statement.FirstName---------DougFredBobSELECT FirstName from TABLE1WHERE FirstName IN ('Doug','Fred','Bob','Mark','Ted',) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-08 : 10:35:39
|
NOT IN ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
ddouma
Starting Member
10 Posts |
Posted - 2007-11-08 : 10:50:57
|
| Won't that return all records that don't have Doug, Fred, Bob, Mark and Ted as the FirstName?I want to know that Mark and Ted where not found in my IN statement |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-08 : 11:04:21
|
The NOT IN statement is executed PER RECORD, not the entire column...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-08 : 11:11:08
|
I think i got what you meantry thisSELECT n.FirstNameFROM ( SELECT 'Doug' AS FirstName UNION ALL SELECT 'Fred' AS FirstName UNION ALL SELECT 'Bob' AS FirstName UNION ALL SELECT 'Mark' AS FirstName UNION ALL SELECT 'Ted' AS FirstName ) n LEFT JOIN TABLE1 t ON n.FirstName = t.FristNameWHERE t.FristName IS NULL
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ddouma
Starting Member
10 Posts |
Posted - 2007-11-08 : 11:51:24
|
| That's exactly what I needed! Thank you very much. DD. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Non-Matches with a SQL IN