Many to one relationships - 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 2008 Forums

Transact-SQL (2008)

Many to one relationships

Author

Topic

Tampapat
Starting Member

18 Posts

Posted - 2012-12-07 : 10:00:36

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-07 : 10:18:50

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

Tampapat
Starting Member

18 Posts

Posted - 2012-12-07 : 11:48:55

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-07 : 11:53:41

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,

Tampapat
Starting Member

18 Posts

Posted - 2012-12-07 : 12:42:26

That seems to be a bit closer, but what I trying to achieve is
Lastname, Firstname, hauser2, INS
INS
INS
INS

Patrick Palmer

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-07 : 12:55:07

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources