Multiple joins in multiple tables

Question

i have the tables below:

patient: id, incidentCode, name, insurance, contactdetailsID
contactDetails: id, cityCode
lookup: id, lookupdescription
incident: id, date

patient joins contactdetails like:

inner join contactdetails on patient.contactdetailsid=contactdetails.id

patient joins incident like:

inner join incident on patient.incidentCode=incident.id

patient joins lookup like:

inner join lookup on patient.insurance = lookup.id

and contact details joins lookup like:

inner join lookup on contactdetails.citycode = lookup.id

and now i want to Select both Lookup.lookupDescription from patient insurance and contactdetails Citycode. How can i do that? At select i also want patient.name, patient.id, incident.date

Have you tried something? Post yout query – Yaroslav Feb 27 '13 at 14:37 — Yaroslav, Feb 27 '13 at 14:37

score 1 · Answer 1 · answered Feb 27 '13 at 14:38

For example

SELECT
  patient.id,
  lookup.lookupdescription,
  contactDetails.cityCode
FROM patient
INNER JOIN contactdetails on patient.contactdetailsid=contactdetails.id
INNER JOIN ...
INNER JOIN ...
INNER JOIN ...
WHERE patient.id = xy

By the way: The last JOIN you provide doesn't look that meaningful to me?! It looks like lookup.id is an citycode and at the same time is an insurance number?!

score 0 · Answer 2 · answered Feb 27 '13 at 14:40

SELECT  d.lookupDescription,
        a.insurance,
        e.Citycode,
        a.name,
        a.id,
        c.date
FROM    patient a
        INNER JOIN contactdetails b
            ON a.contactdetailsid = b.id
        INNER JOIN  incident c
            ON a.incidentCode = c.id
        INNER JOIN lookup d
            ON a.insurance = d.id
        INNER JOIN contactdetails e
            ON e.citycode = d.id

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

Multiple joins in multiple tables

2 Answers2