| Author |
 Topic  |
|
|
Tampapat
Starting Member
USA
18 Posts |
 Posted - 12/07/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/07/2012 : 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
USA
18 Posts |
Posted - 12/07/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/07/2012 : 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
USA
18 Posts |
Posted - 12/07/2012 : 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
Flowing Fount of Yak Knowledge
3821 Posts |
|
| |
Topic |
|
|
|
Many to one relationships
