Select specific records within a single query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2012 Forums

Transact-SQL (2012)

Select specific records within a single query?

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2013-08-17 : 23:28:46

So, I need to produce records based on the following criteria but the catch is, the prior developer HARDCODED his queries into his c# code. So, as bad as it is, I'll need to follow the same pattern, and simply hardcode a single query string into the c# code. No stored procedure:

Pull all client names a specific employee is responsible for

SELECT all client_name's from Client's table where employee_id = 356 & join to ClientContact's table

IF the ClientContact table's "PrimayContact" field = 1 (true), then only return that one record

ELSE IF there are no PrimaryContact's = 1, return all client_name's belonging to that employee

CLIENT table
------------------------
client_id | client_name | employee_id
-----------------------------------------------
1 | ABC Company | 356
2 | Main Co Inc | 356
3 | Acme Co | 356

ClientContact table
--------------------------
client_contact_id | client_id | contact_name | PrimaryContact
-------------------------------------------------------
1 | 1 | John | false
1 | 1 | Tony | false
1 | 1 | Amy | false
2 | 2 | Stephanie | true
2 | 2 | April | false
3 | 3 | Steve | false
3 | 3 | Mike | true

In the above sample table, client_id #1 (ABC Company, should return all 3 records because there is no PrimaryContact). In client #'s 2 & 3 however, there are primary contacts, so they should each only return 1 record.

Any idea how I could do this in a single query? I hope I explained it clearly.

Thanks in advance.

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-18 : 00:45:27

[code]

;with ClientContact AS
( select 1 as client_contact_id, 1 as client_id,'John' as contact_name,0 as PrimaryContact union all
select 1 , 1 , 'Tony' , 0 union all
select 1 , 1 , 'Amy' ,0 union all
select 2 , 2 , 'Stephanie' , 1 union all
select 2 , 2 ,' April' , 0 union all
select 3 , 3 , 'Steve' , 0 union all
select 3 , 3 , 'Mike' , 1
)
, CLIENT AS
(select 1 as client_id, 'ABC Company' as client_name , 356 as employee_id union all
select 2 , 'Main Co Inc' , 356 union all
select 3 , 'Acme Co' , 356
)

select c.client_name,c1.contact_name
from ClientContact c1
inner join Client as c on c.client_id=c1.client_id
where PrimaryContact=1
or (not exists ( select client_id from ClientContact c2 where c1.client_id=c2.client_id and PrimaryContact=1))

[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-18 : 04:27:02

[code]
SELECT client_name ,contact_name
FROM
(
SELECT MIN(CASE WHEN PrimaryContact = 'true' THEN contact_name ELSE NULL END) AS PrimaryContact,c.client_name ,contact_name
FROM CLIENT c
INNER JOIN ClientContact cc
ON cc.client_id = c.client_id
)t
WHERE PrimaryContact = contact_name
OR PrimaryContact IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-20 : 11:52:07

ORs kill performance. On a large dataset, this should outperform the other solutions:

-------------------------------------------------------
-- TEST DATA
-------------------------------------------------------
;with ClientContact 
(client_contact_id,client_id,contact_name,PrimaryContact)
AS
( select 1 , 1 , 'John' ,0 union all
  select 1 , 1 , 'Tony' , 0 union all
  select 1 , 1 , 'Amy' ,0 union all
  select 2 , 2 , 'Stephanie' , 1 union all
  select 2 , 2 ,' April' , 0 union all
  select 3 , 3 , 'Steve' , 0 union all
  select 3 , 3 , 'Mike' , 1	
  )
, CLIENT 
(client_id,client_name,employee_id)
AS
(select 1 , 'ABC Company' ,  356 union all
 select 2 , 'Main Co Inc' , 356 union all
 select 3 , 'Acme Co' , 356
)
-------------------------------------------------------
-- SOLUTION
-------------------------------------------------------
, cteClientContactPrimary
AS
(
select		c1.client_contact_id,
		c1.client_id,
		c1.contact_name,
		c1.PrimaryContact,
		MAX(c1.PrimaryContact) OVER (PARTITION BY client_id) AS hasPrimaryContact
from		ClientContact c1
)
select		c.client_name,
		c1.contact_name 
from		Client as c 
inner join	cteClientContactPrimary AS c1 
ON		c.client_id=c1.client_id 
WHERE		c1.hasPrimaryContact = c1.PrimaryContact

/*
client_name	contact_name
ABC Company	Tony
ABC Company	Amy
ABC Company	John
Main Co Inc	Stephanie
Acme Co		Mike
*/

* edit: added output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources