Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Goalie35
Yak Posting Veteran

81 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
Aged Yak Warrior

Romania
545 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
52326 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
343 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  
 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.02 seconds. Powered By: Snitz Forums 2000