CODEWITHSUNDEEP
mysqlms-access
Shashank
Shashank

Reputation: 6337

Multiple Inner Join on different tables in MS ACCESS

HI i have a patient information table and a master look up table, patient info contains the value data and its text is fetched from mstMasterLookup table. The following code sample was working fine in mysql but in access its causing error.

can any one provide suggestion on this. how to acheive this functionality in Access

    SELECT 
   PatientInformations.[Study_Id], 
   tblHospital.Text_data as Hospital, 
   tblGender.Text_data as Gender
FROM 

PatientInformations

INNER JOIN 

(SELECT text_data,Value_data
            FROM mstMasterLookup
            WHERE mstMasterLookup.Table ='Hospital'
            AND Is_Active = true) tblHospital
                ON tblHospital.Value_data =cstr( PatientInformations.Hospital_Id)

INNER JOIN  (SELECT text_data,Value_data
            FROM mstMasterLookup
            WHERE mstMasterLookup.Table ='Gender'
            AND Is_Active = true) tblGender
ON tblGender.Value_data =cstr( PatientInformations.Gender);

Upvotes: 0

Views: 1357

Answers (1)

Clon
Clon

Reputation: 1055

Try this

SELECT P.Study_Id, 
       H.text_data AS Hospital, 
       G.text_data AS Gender
FROM (PatientInformations AS P 
   INNER JOIN (SELECT text_data, Value_data 
               FROM mstMasterLookup 
               WHERE Is_Active and Table="Hospital") AS H 
   ON P.Hospital_Id=CSTR(H.Value_data)) 
   INNER JOIN (SELECT text_data, Value_data 
               FROM mstMasterLookup 
               WHERE Is_Active AND Table="Gender") AS G 
   ON P.Gender=CSTR(G.Value_data);

While this works, you should be careful when editing it, since Access doesn't understand this syntax, and keeps changing parenthesis () into [].

I think that the problem was that you didn't use the "AS" clause for the table aliases.

Upvotes: 1

Related Questions