CODEWITHSUNDEEP
mysqlsqldatabase
Tim
Tim

Reputation: 45

How to join across 3 tables when one isn't a 'main' one

I wish to use a join across some tables but am having some difficulty finding a solution.

Take this SQL (stripped down):

SELECT
*
FROM project_contacts c
LEFT JOIN projects p ON c.project=p.ID
LEFT JOIN project_contact_type ON people.project_contact_type=project_contact_type.ID
LEFT JOIN people ON c.person=people.ID
WHERE p.live = 1
ORDER by p.code
LIMIT 4;

returns the error:

Unknown column 'people.project_contact_type' in 'on clause'

From the line: LEFT JOIN project_contact_type ON people.project_contact_type=project_contact_type.ID

which is the issue, 'people.project_contact_type' is found based on the 'people' table and not the 'project_contacts' table which is the one where the other joins are off, if that makes sense??

So I don't know how to do a join inside of another join if thats even what I'm trying to do ;) ...

Upvotes: 1

Views: 56

Answers (2)

Sparky
Sparky

Reputation: 15105

Try this:

SELECT *
FROM project_contacts c
LEFT JOIN projects p ON c.project=p.ID
LEFT JOIN people ON c.person=people.ID
LEFT JOIN project_contact_type ON people.project_contact_type=project_contact_type.ID
WHERE p.live = 1
ORDER by p.code
LIMIT 4;

Upvotes: 0

xQbert
xQbert

Reputation: 35343

Order of the joins matters: the system doesn't know about the people, so it needs to be 2nd in your list.

SELECT
*
FROM project_contacts c
LEFT JOIN projects p 
  ON c.project=p.ID
LEFT JOIN people 
 ON c.person=people.ID
LEFT JOIN project_contact_type 
  ON people.project_contact_type=project_contact_type.ID
WHERE p.live = 1
ORDER by p.code
LIMIT 4;

Upvotes: 3

Related Questions