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 2008 Forums
 Transact-SQL (2008)
 Many to one relationships
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tampapat
Starting Member

USA
18 Posts

Posted - 12/07/2012 :  10:00:36  Show Profile  Reply with Quote
I have a query issue in which I am trying to pull physicians names po.poid from the Physician (PO Table) and there status at our facilities (HA Table). However, when I pull them using my query i get the same doctors listed for each facility. So the same doctor can have the same status at multiple facilities. I just need to pull the doctors names and the status. i also want to include insurance data (INS Table). All Dr's have the same insurance at every facility.

HELP

SELECT
po.POID,
polastname,
pofirstnam,
hauser2,
ininsname

FROM PO
Join IA
on ia.poid = po.poid
Join HA
on ha.poid = po.poid
Join INS
on ia.inid = ins.inid

GROUP BY
po.poid,
polastname,
pofirstnam,
hauser2,
ininsname

Patrick Palmer

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/07/2012 :  10:18:50  Show Profile  Reply with Quote
Take two rows with the same doctor, and look up which column is not the same. From what you described it sounds like it may be hauser2. If that is so, either exclude that column, or put that in an aggregate function - for example like this:
SELECT
po.POID,
polastname,
pofirstnam,
MAX(hauser2) AS OneofTheHAUsers,
ininsname

FROM PO
Join IA
on ia.poid = po.poid
Join HA
on ha.poid = po.poid
Join INS
on ia.inid = ins.inid

GROUP BY 
po.poid,
polastname,
pofirstnam,
-- hauser2,
ininsname
Go to Top of Page

Tampapat
Starting Member

USA
18 Posts

Posted - 12/07/2012 :  11:48:55  Show Profile  Reply with Quote
That seems to do the trick, but when I add the ininsname, I'm back to 55,000 when it should be about 4,000...

Patrick Palmer
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/07/2012 :  11:53:41  Show Profile  Reply with Quote
quote:
Originally posted by Tampapat

That seems to do the trick, but when I add the ininsname, I'm back to 55,000 when it should be about 4,000...

Patrick Palmer



Is it this then?

SELECT
po.POID,
polastname,
pofirstnam,
MAX(hauser2) AS OneofTheHAUsers,
MAX(ininsname)ininsname

FROM PO
Join IA
on ia.poid = po.poid
Join HA
on ha.poid = po.poid
Join INS
on ia.inid = ins.inid

GROUP BY 
po.poid,
polastname,
pofirstname
-- hauser2,
Go to Top of Page

Tampapat
Starting Member

USA
18 Posts

Posted - 12/07/2012 :  12:42:26  Show Profile  Reply with Quote
That seems to be a bit closer, but what I trying to achieve is
Lastname, Firstname, hauser2, INS
INS
INS
INS


Patrick Palmer
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4347 Posts

Posted - 12/07/2012 :  12:55:07  Show Profile  Reply with Quote
Post DDL, DML and Expected output so we can help you better. Here are some links that can help you supply that information is you are not sure how:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
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.05 seconds. Powered By: Snitz Forums 2000