SQL Query - Ok its from a windows app

  • Thread starter Thread starter mithril
  • Start date Start date
M

mithril

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 from
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 FROM
(profileTable INNER JOIN patienttable ON profiletable.PatID=
patienttable.PatID) GROUP BY patienttable.patsurname HAVING
(patienttable.patactive = 1)

I had to add the Group by statement when I tried to add this into
another query.

I dont have direct access to the DB so cannot use stored queries.
 
I have a query that gets the desired results in ACCESS but now the
DataAdapter doesn't like it.

Internally it runs (from the wizard) but I cannot make a dataset from
it!?

man this is frustrating.

BTW the SQL I came up with is as follows
(note i discover I don't need table1)

SELECT Profiletable.propnameid, Profiletable.CodeID,
Profiletable.patientid, MedicineTable.MedicineInfo
FROM MedicineTableRIGHT JOIN (Profiletable INNER JOIN [SELECT
Profiletable.patientid, Max(Profiletable.CodeID) AS MaxOfCodeID
FROM Profiletable GROUP BY Profiletable.patientid]. AS E1 ON
Profiletable.CodeID = E1.MaxOfCodeID) ON MedicineTable.MedicineID =
Profiletable.MedicineID;
 
mithril,
In general the wizards don't handle joined results correctly as they
can't figure out how to write the Update/Insert/Delete commands from the
SELECT. You can manually create a DataAdapter and run code to load the
resulting table into a DataSet though.

Ron Allen
 
Back
Top