Query XREF table - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query XREF table

Author

Topic

skes1
Starting Member

4 Posts

Posted - 2011-02-04 : 14:39:54

Looking for an SQL statement to query a cross reference table and pull back results where an employee "does not have" a specific certification ID.

The table has two columns: EMP_ID and CERT_ID.

EMP_ID CERT_ID
21 15
21 16
21 17
25 12
25 11

How does one structure the SQL query so that Employees who do not have CERT_ID '17' are removed from the return results completely? Even though they exist in the table with other CERT_ID's associated with them? I keep getting multiple listings of the same employee in the return results even though they have been filtered as not having the CERT_ID.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-04 : 14:48:31

Please post complete sample data from all tables involved. Showing us one table where the data exists isn't enough. Also include expected result set.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-04 : 14:51:24

SELECT t1.*
FROM yourTable t1
LEFT JOIN
(select emp_Id from yourTable where CertId = '17' group by emp_id) t2
on t1.Emp_ID = t2.Emp_Id
WHERE t2.emp_id is null

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-04 : 15:35:18

SELECT Emp_ID
FROM dbo.Table1
GROUP BY Emp_ID
HAVING MAX(CASE WHEN Cert_ID = 17 THEN 1 ELSE 0 END) = 0

N 56°04'39.26"
E 12°55'05.63"

skes1
Starting Member

4 Posts

Posted - 2011-02-05 : 08:31:07

Thanks Peso, that worked perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources