t-sql where not in

The only way I can explain it is with an example:
table one
smith sanantonio
smilth phoenix
jones sanantonio
thompson sanantonio
lurch buffalo

table 2
smith apple
jones apple
thompson apple

Basically table one has ALL records and table 2 is a subset of table one but all with sanantonio.  I want to know who in table 2 has only the one record in table 1.  The result set would be
jones sanantonio
thompson sanantonio

since they have only the one record in table 1.  I've attached my script and it returns 0 records and I know there should be some.
SELECT     [email business]
FROM         sanAntonio
WHERE     [email business] NOT IN
                          (SELECT     [email business]
                            FROM          summitssince05
                            WHERE      eventname <> 'SanAntonio')

Open in new window

KathleenRAsked:
Who is Participating?
 
reb73Connect With a Mentor Commented:
Here you go-

(replace tablenames and fieldnames below with the correct identifiers)
SELECT	t2.field1 , t1.field2
FROM table2 t2
INNER JOIN table1 t1 ON t1.field1 = t2.field1 AND t1.field2 = 'sanantonio'
WHERE EXISTS (SELECT t3.field1, COUNT(t3.field2) 
		  FROM table1 t3
		  WHERE t3.Field1 = t2.field1
		  GROUP BY t3.field1
		  HAVING COUNT(t3.field2) = 1
		 )
 

Open in new window

0
 
reb73Commented:
Is 'smith sanantonio' a value in a single column or are they two separate columns in table1?
0
 
KathleenRAuthor Commented:
2 separate columns
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
KathleenRAuthor Commented:
BEAUTIFUL!!!  Thanks so much, never would have gotten that, you've saved me hours.
0
 
8080_DiverCommented:
A better solution might be the following code.  The use of an EXISTS clause can have negative consequences if your table is of any size because of the resulting execution plan.  

SELECT T1.[email business], T1.City
FROM Table1 T1
INNER JOIN
(SELECT T1.[Email Business], COUNT(T1.EmailBusiness]) AS Occurrences
 FROM Table1 T1
 INNER JOIN Table2 T2
 ON T1.[Email Business] = T2..[Email Business]
 GROUP BY T1..[Email Business]
) Z
ON Z.[Email Business]=T1.[Email Business]
WHERE Occurrences = 1;

Open in new window

0
 
8080_DiverCommented:
Oops, just noticed a slight typo in the subselect . . . there are a couple of spots that have two periods where there should be only one.
SELECT T1.[email business], T1.City
FROM Table1 T1
INNER JOIN
(SELECT T1.[Email Business], COUNT(T1.EmailBusiness]) AS Occurrences
 FROM Table1 T1
 INNER JOIN Table2 T2
 ON T1.[Email Business] = T2.[Email Business]
 GROUP BY T1.[Email Business]
) Z
ON Z.[Email Business] = T1.[Email Business]
WHERE Occurrences = 1;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.