We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

t-sql where not in

KathleenR
KathleenR asked
on
Medium Priority
1,211 Views
Last Modified: 2013-11-24
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

Comment
Watch Question

Commented:
Is 'smith sanantonio' a value in a single column or are they two separate columns in table1?

Author

Commented:
2 separate columns
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
BEAUTIFUL!!!  Thanks so much, never would have gotten that, you've saved me hours.
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

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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.