Hi,
Well, I dunno if it's what U asked for, but I interpret what you're
asking is to add the additional join of the Medicine table to your
subquery. One way of doing it as follows;
SELECT CodeID, patsurname, MedicineTable.MedicineID,
OtherInfo, MedicineTable.MedicineInfo
FROM (SELECT MAX(ProfileTable.CodeID) AS CodeID,
OtherInfo, patienttable.patsurname,
ProfileTable.MedicineID AS MedID
FROM profileTable
INNER JOIN patienttable
ON profiletable.PatID = patienttable.PatID
GROUP BY patienttable.patsurname,
ProfileTable.MedicineID, OtherInfo ), MedicineTable
WHERE MedicineID = MedID
HTH, and hoping I didn't miss the boat on what it was you
were asking for, as I just took a wild-eyed guess.
K.C
mithril wrote:[color=blue]
> I cannot get my head around this query...
>
> I think I need a nested query but here's the problem. I promise i've
> exerted my meager brain power on this problem & ask this as a last
> resot!
>
> 3 Tables
> PatientTable
> PatActive, PatID
> ProfileTable
> PatID, CodeID, OtherInfo, MedicineID
> MedicineTable
> MedicineID MedicineInfo
>
>
>
> I need a return from Table2 Info including the detailed child data[/color]
from[color=blue]
> Table3 [OtherInfo] where patient is active, however i only want rows
> from Profile table where the element CodeID is MAX for each patient.
>
> subquery is fine
> SELECT MAX(ProfileTab.CodeID) AS CodeID , patienttable.patsurname[/color]
FROM[color=blue]
> (profileTable INNER JOIN patienttable ON profiletable.PatID=
> patienttable.PatID) GROUP BY patienttable.patsurname HAVING
> (patienttable.patactive = 1)
>
>
> I dont have direct access to the DB so cannot use stored queries.
>
> I've got the 2 queries to work seperately but am unsure how to relate
> the CodeID from each to filter only for the MAX CodeID rows!?
>
> Problem is the original SQL statement relationships are allready
> convoluted
>
> Medicine RIGHT JOIN (patienttable LEFT JOIN profileTable ON
> patienttable.patid = profiletable.patid) ON medicine.medicineID=
> profiletable.medicineid
>
> so i think i need to add somethin like
> (profiletable left join(The above select statement) as S1 on
> profiletable.codeid=e1.codeid)
>
> Appreciate any help (even "Give up")
>
> worse case scenario I do 2 queries & iterate thru each in the program
> selecting only the max codeid!?
>
> MA
>
> *** Sent via Developersdex
http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]
