December 1, 2007 at 3:12 am
Hi all, if anyone can help me with this it would be great. it has been driving me crazy and i'm sure that their is an easy solution. I am using oracle, and have the following relations.
Patient(Patno, name,docno*)
Doctor(Docno, name, specialization)
Treatment(treatno, startdate, reason, docno*, patno*)
I want to list every patients number and name, and the number of treatments given by a certain doctor (e.g doctor name = 'smith'). the problem i am having is that if i specify the doctor is WHERE clause it only gives me the doctors who are under 'smith' but i want all even if 'smith' has given them 0 treatments. below is code that i have so far to view all patients with corresponding data.
select pa.patno, pa.lname, tr.treatno
from patient pa full join treatment tr on tr.patno=pa.patno;
if anyone can help that would be amazing, Thx
December 1, 2007 at 4:34 am
SELECTP.patno, P.name, P.docno, P.docname, P.specialization, COALESCE( T.NoOfTreatments, 0 ) AS NoOfTreatments
FROM(
SELECTP.patno, P.name, D.docno, D.docname, D.specialization
FROMdbo.Patient P
CROSS JOIN dbo.Doctor D
) P
LEFT JOIN
(
SELECTpatno, docno, COUNT( * ) AS NoOfTreatments
FROMdbo.treatment
) T ON P.patno = T.patno and P.docno = T.docno
--Ramesh
December 1, 2007 at 5:07 am
HI thx for your reply,
i have treid you code but get error:
SELECTP.patno, P.name, P.docno, d.lname, d.specialization, COALESCE( T.NoOfTreatments, 0 ) AS NoOfTreatments
*
ERROR at line 1:
ORA-00918: column ambiguously defined.
any thoughts, thank you v much for helping.
December 1, 2007 at 6:50 am
Obviously, this is SQL Server forum, the solutions provided would only be compatible with SQL Server 2000 or higher.
Why dont you try posting it in an Oracle forum? You can start from [highlight]here[/highlight]
--Ramesh
December 1, 2007 at 9:22 am
Oracle is very picky about the reuse of table aliases within a given query... try this...
SELECTP.patno, P.name, P.docno, P.docname, P.specialization, COALESCE( T.NoOfTreatments, 0 ) AS NoOfTreatments
FROM(
SELECTPa.patno, Pa.name, D.docno, D.docname, D.specialization
FROMdbo.Patient Pa
CROSS JOIN dbo.Doctor D
) P
LEFT JOIN
(
SELECTpatno, docno, COUNT( * ) AS NoOfTreatments
FROMdbo.treatment
) T ON P.patno = T.patno and P.docno = T.docno;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2007 at 9:27 am
And, I'm not so sure about the need for the cross join... but I'm on my way out for the day...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply