Help with Lookup Query - 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)

Help with Lookup Query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-05-04 : 07:45:39

OK, I am asking for help with an odd query request (odd for me).

Let's say I have a "contact" table that consists of the following columns: "contactid", "lastname", "firstname", "roleid".

My example really consists of two tables (contact and contactrolebridge) that I would join to, but for simplification I describe them as one contact table.

What I want to do is look up a contact, and if that contact has a roleid of 22 or 23, do not show ANY of his associated contact records. So, for the below data, the only record I want to return is row 4 and 5. Does this make sense?

Sample data for the table is as follows:


contactid   lastname   firstname   roleid
1           smith      john        9
2           smith      john        22
3           smith      john        23
4           smith      paul        9
5           smith      paul        12

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-04 : 08:19:29

[code]
select *
from contact c
where not exists
(
select *
from contact x
where x.lastname = c.lastname
and x.firstname = c.firstname
and x.roleid in (22,23)
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-05-04 : 09:13:43

Great, this is exactly what I was looking for!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:11:19

Also:-

SELECT contactid,   lastname,   firstname,   roleid
FROM
(
SELECT COUNT(CASE WHEN roleid IN (22,23) THEN 1 ELSE 0 END) OVER (PARTITION BY lastname, firstname) AS Occur,
contactid,   lastname,   firstname,   roleid
FROM Table 
)t
WHERE Occur =0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-05-05 : 07:20:00

Indeed, thanks visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 07:32:45

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources