JulianJ JulianJ - 4 months ago 7
MySQL Question

How to join all three tables in MYSQL?

I have a MySQL database that has three tables holding information about uploaded photos by users. I have a PHP page that displays all the photos in the database (

tbl_uploads
) and who uploaded them (
tbl_users
). To display the photos and who uploaded them I have a join in the MySQL query.

SELECT *
FROM tbl_uploads, tbl_users
WHERE tbl_uploads.user_id = tbl_users.user_id
ORDER BY date DESC


I now want to join a third table
tbl_collab
to the MySQL query that allows me to display all the users that collaborated with the photo (a form allows them to post the
$file
and their $
user_id
to
tbl_collab
). I guess I need to add a join from
tbl_uploads.file
with
tbl_collab.user_id
but I'm not sure how.

tbl_users
|//**user_id**//|int(11)|No|
|user_name|varchar(25)|No|
|user_email|varchar(60)|No|
|user_password|varchar(255)|No|
|joining_date|datetime|No|

tbl_uploads

|//**id**//|int(10)|No|
|file|varchar(100)|No|
|type|varchar(30)|No|
|size|int(11)|No|
|user_id|int(11)|No|
|user_name|varchar(25)|No|

tbl_collab

|//**id**//|int(11)|No|
|user_name|varchar(100)|No|
|user_id|int(11)|No|
|file|varchar(255)|No|


I have been trying your various suggestions and I can't really get them to work as I would hope so I have made a mysql fiddle that might be help me.
The problem is that when I loop through the rows that the query throws up in PHP I ether get just the rows where there is join with
tbl_uploads.file and tbl.collab.file
or I get the multiple rows duplicating themselves.

Answer

The first thing to do is to normalize your data. If you look closely, username appears in all three tables. It shouldn't. It belongs only in the users table. Then your other tables need to have a user_id field instead of the username.

tbl_uploads

|//**id**//|int(10)|No|
|file|varchar(100)|No|
|type|varchar(30)|No|
|size|int(11)|No|
|user_id|int(11)|No|

 tbl_collab

|//**id**//|int(11)|No|
|user_id|int(11)|No|
|file|varchar(255)|No|

In both cases the user_id is a foreign key to the id field in the users table. Now we have something consistent to join on.

SELECT * FROM tbl_uploads
  INNER JOIN tbl_users ON tbl_uploads.user_id = tbl_users.user_id
  INNER JOIN tbl_collab ON tbl_collab.file = tbl_uploads.file

Whether you should use INNER JOIN or LEFT JOIN depends on exactly what you need to do with your data, but INNER JOIN seems more appropriate based on information provided.

Update: As @drew pointed out, none of your tables have a column named date did you perhaps intend to sort by tbl_users.joining_date?