Help with case - 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 case

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-01-11 : 10:25:59

I am trying to show all contacts and their role. When the contact has a duplicate role, show those contacts. If there are no duplicate, then I want to show that contact.

Here is what I have so far, any help would be appreciated.

select com.mcic, com.company_name, cont.first_name, cont.last_name, c_role.contact_role, count(c_role.contact_role) as contact_role_size,
case when COUNT(c_role.contact_role) > 1 then cont.first_name -- show all contacts
else cont.first_name -- show only that contact
end as CONTACT_RETUNRED_FROM_CASE
from productioned.dbo.company com
INNER JOIN productioned.dbo.contact cont ON com.company_id = cont.company_id
INNER JOIN productioned.dbo.contact_role_bridge bridge ON cont.contact_id = bridge.contact_id
INNER JOIN productioned.dbo.contact_role c_role ON c_role.contact_role_id = bridge.contact_role_id
GROUP BY c_role.contact_role, com.mcic, com.company_name, cont.first_name, cont.last_name, cont.status
HAVING (COUNT(c_role.contact_role) > 1 and com.mcic is not null and c_role.contact_role = 'President' and cont.status = 'Active')

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-01-11 : 10:36:28

Hi,

If u want to show the contact's as comma separated then use FOR XML PATH('')
else remove the case then it will display all the Contacts.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-01-11 : 10:38:54

I am really trying to display all contacts with a duplicated role of 'President'. If there is only one contact with a role of 'President', then display only that one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 10:55:01

quote:
Originally posted by qman

I am really trying to display all contacts with a duplicated role of 'President'. If there is only one contact with a role of 'President', then display only that one.

seems like you simply want the name of all contacts having a role of president.Can you confirm if this is your requirement?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-01-11 : 10:57:23

try moving the code
and com.mcic is not null and c_role.contact_role = 'President' and cont.status = 'Active'
to WHERE part of the qry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources