CODEWITHSUNDEEP
mysqlsql
Fawad Mirzad
Fawad Mirzad

Reputation: 5

How to get contents of a tables based on left join on two other tables

I have a categories table and a files table. there is a category_record_mm table for storing the relationships. I want to get categories of a file, for that I have written a JOIN statement, but JOIn is complaining that files.uid is not a valid column. what I actuallay want is, it should give me all categories if category_record_mm.foreign_uid is equal to some id. This is what I have written :

SELECT * FROM `categories`
LEFT JOIN `category_record_mm` ON `category_record_mm`.`foreign_uid` = `files`.`uid`
WHERE (`category_record_mm`.`foreign_uid` = 123 )

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you just want the categories, you do not need to reference the files table. You simply need to phrase the query correctly:

SELECT c.*
FROM categories c JOIN
     category_record_mm cr
     ON cr.categories_uid = c.uid
WHERE cr.foreign_uid = 123;

Notes:

  • The JOIN between the two table is on the category columns, not the file columns.
  • Table aliases make the query much easier to write and to read.
  • You have the file uid in category_record_mm. You are filtering on that, so you don't need to join the files table.

Upvotes: 1

Cid
Cid

Reputation: 15257

You are missing 1 JOIN, the one between categories and category_record_mm

This is like having 3 towns, A, B and C, with a bridge between A and B and another one between B and C. You can't go from A to C without using the bridge between A and B

SELECT * FROM `categories`
LEFT JOIN `category_record_mm` ON `category_record_mm`.`categories_uid` = `categories`.`uid`
LEFT JOIN `files` ON `category_record_mm`.`foreign_uid` = `files`.`uid`
WHERE (`category_record_mm`.`foreign_uid` = 123 )

categories_uid being the foreign key referencing the table categories and categories.uid being the primary key of categories

Upvotes: 1

Related Questions