user2924019 user2924019 - 1 month ago 10
MySQL Question

MYSQL - Join three tables (in a row?)

I am having trouble with coming up with the query required to do what I am after.

I have three tables like this:

client_files

-----------------------
client_id file_id
-----------------------
1 2
1 3
1 6
2 1
2 2
3 5



files

-------------------------------------------------
ID file_name file_category_id
-------------------------------------------------
1 file1.ext 1
2 file2.ext 3
3 file3.ext 1
4 file4.ext 1
5 file5.ext 2
6 file6.ext 2



file_categories

--------------------------
ID category_name
--------------------------
1 category1
2 category2
3 category3


I am attempting to build a query which will return the filename and category name for a particular client ID.

The result I am expecting is (from ID 1):

-----------------------------
file_name category_name
-----------------------------
file2.ext category3
file3.ext category1
file6.ext category2


As far as I understand it, I need to join the
client_files
table to the
files
table and then to the
file_categories
table. I've looked at other examples which are about joining two tables with a 3rd table linking them together, but believe this is a different situation.

This is the equivalent of what I have come up with, but the results are almost random, with some files returned that are not linked, and some are missing.

SELECT
f.file_name,
fc.category_name
FROM
client_files cf,
files f,
file_categories fc
WHERE
cf.client_id = 1 AND f.ID = cf.file_id AND fc.ID = f.file_category_id;

RP- RP-
Answer

You need to have proper join conditions, right now you are doing cross join and then having the where condition.

SELECT
  cf.client_id,
  f.file_name,
  fc.category_name
FROM
  client_files cf
LEFT JOIN
  files f ON cf.file_id = f.ID
LEFT JOIN
  file_categories fc ON fc.ID = f.file_category_id
WHERE
  cf.client_id = 1;