SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select specific records within a single query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Goalie35
Yak Posting Veteran

78 Posts

Posted - 08/17/2013 :  23:28:46  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 08/18/2013 :  00:45:27  Show Profile  Reply with Quote



;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))





S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/18/2013 :  04:27:02  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 08/20/2013 :  11:52:07  Show Profile  Reply with Quote
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

Edited by - lazerath on 08/20/2013 11:53:16
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000