SQL exist query

Snapshot of data:
num                               type                       f_id
7654321                           1                          7
7654321                           1                         2
7654321                           70                         3
1234567                           1                          2
1234567                           70                         3
1234567                           70                         2

I need the sql query that returns the num when the num is not associated to f_id = 7:
In this example: it should return 1234567
needpleaseAsked:
Who is Participating?
 
agusacilCommented:
This should get what you need :

select distinct(num) from tablename t1 where not exists (select num from tablename t2 where t2.ID = 7 and t2.num = t1.num)

Open in new window

0
 
Simone BSenior E-Commerce AnalystCommented:
If I understand correctly what you're looking for, this should work:

SELECT num FROM tbl WHERE num NOT in
(SELECT num FROM tbl
WHERE f_id = 7)
0
 
Simone BSenior E-Commerce AnalystCommented:
Actually, you should probably add DISTINCT to the previous query:

SELECT DISTINCT num FROM tbl WHERE num NOT in
(SELECT num FROM tbl
WHERE f_id = 7)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
needpleaseAuthor Commented:
The query still returns both numbers:

7654321
1234567
0
 
deightonprogCommented:
what does this return?

SELECT distinct num FROM tbl
WHERE f_id = 7

1234567 - should not be in there, if it is, then that explains it

TRY

SELECT DISTINCT tbl.num FROM tbl WHERE NOT EXISTS
(SELECT 0 FROM tbl  n2
WHERE n2.f_id = 7 AND n2.num = tbl.num)
0
 
needpleaseAuthor Commented:
Perfect
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.