• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1191
  • Last Modified:

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

0
KathleenR
Asked:
KathleenR
  • 2
  • 2
  • 2
1 Solution
 
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
 
reb73Commented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now