CODEWITHSUNDEEP
phpmysqljoin
Dan
Dan

Reputation: 103

How to select from 3 tables whilst joining to 2 others

I have a table called lwim_projects with the column project_id (amongst others), a table called lwim_designers with the column designer_id (amongst others) and a table called lwim_images with the column image_id (amongst others).

I also have 2 joining tables called lwim_join_designers_projects and lwim_join_projects_images.

The lwim_join_designers_projects has the columns:

The lwim_join_projects_images has the columns:

I would like to join the joining tables with the tables mentioned earlier but unfortunately cannot seem to be able to join both, only one of them.

The code below looks right to me (and have tried many, many combinations of this).

$result = mysql_query("SELECT * FROM lwim_projects, lwim_designers, lwim_images
INNER JOIN lwim_join_designers_projects ON lwim_join_designers_projects.designer_id = lwim_designers.designer_id
INNER JOIN lwim_join_projects_images ON lwim_join_projects_images.image_id = lwim_images.image_id
WHERE lwim_projects.project_id = lwim_join_designers_projects.project_id
AND lwim_projects.project_id = lwim_join_projects_images.project_id
ORDER BY date_repinned DESC
LIMIT 20");

I have also tried it without the second where statement.

Like I said it works without selecting from the lwim_images table and joining the images table and visa versa but doesn't seem to want to work for both.

Can anyone shed some light on where I may be going wrong?

Kind Regards, Daniel

Upvotes: 1

Views: 81

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

You are joining 5 tables. That generally means you need 4 INNER JOINS, unless you are joining in an unusual way. Try rewriting your query with just one table in the FROM clause followed by four INNER JOIN clauses. Each INNER JOIN needs an ON condition.

If I were debugging this, I would try getting the query right using interactive SQL before embedding the query in code.

Edit to include answer (untested):

$result = mysql_query("SELECT * 
FROM lwim_designers
INNER JOIN lwim_join_designers_projects ON
 lwim_join_designers_projects.designer_id = lwim_designers.designer_id
INNER JOIN lwim_projects ON 
  lwim_projects.project_id = lwim_join_designers_projects.project_id
INNER JOIN lwim_join_projects_images ON
  lwim_join_projects_images.project_id = lwim_projects.project_id
INNER JOIN lwim_images ON
  lwim_images.image_id = lwim_join_projects_images.image_id 
ORDER BY date_repinned DESC
LIMIT 20");

It's awfully hard with these names to makes sure I haven't made a typo.

Upvotes: 2

Mathlight
Mathlight

Reputation: 6653

Try this:

$result = mysql_query("SELECT * FROM lwim_projects, lwim_designers, lwim_images
INNER JOIN lwim_join_designers_projects ON lwim_join_designers_projects.designer_id = lwim_designers.designer_id AND lwim_join_designers_projects.project_id = lwim_projects.project_id
INNER JOIN lwim_join_projects_images ON lwim_join_projects_images.image_id = lwim_images.image_id AND lwim_join_projects_images.project_id = lwim_projects.project_id
ORDER BY date_repinned DESC
LIMIT 20");

I've placed the where clause inside the INNER JOIN ON statement, mayby that helps...

Upvotes: 1

Related Questions