SQL左连接查询问题：files.uid无效报错及文件分类查询需求

Hey, let's break down what's going wrong here and fix it up!

First, the error explanation #

That files.uid is not a valid column error is happening because your SQL doesn't include the files table at all—you tried referencing it in the JOIN condition, but the database has no idea what files you're talking about.

Aligning with your actual need #

You said you want to get all categories linked when category_record_mm.foreign_uid equals a specific ID (123 here). Good news: you don't even need the files table for this! The foreign_uid in the junction table already ties directly to the file ID, so you just need to join categories with category_record_mm correctly.

Fixed SQL Query #

SELECT categories.*
FROM `categories`
INNER JOIN `category_record_mm` 
  ON `category_record_mm`.`category_uid` = `categories`.`uid`
WHERE `category_record_mm`.`foreign_uid` = 123;

A few quick notes: #

• I used INNER JOIN instead of LEFT JOIN because your WHERE clause filters for rows where foreign_uid is 123—any category without a matching junction entry would get excluded anyway, so INNER JOIN is more efficient here. If you ever need to include all categories (even those not linked to this file), switch back to LEFT JOIN.
• I assumed the column linking category_record_mm to categories is category_uid—if it's named something else (like cid), just replace that with your actual column name.

If you DO need file details too... #

If later you want to pull in info from the files table along with the categories, here's how you'd adjust it:

SELECT categories.*, files.*
FROM `categories`
INNER JOIN `category_record_mm` 
  ON `category_record_mm`.`category_uid` = `categories`.`uid`
INNER JOIN `files`
  ON `files`.`uid` = `category_record_mm`.`foreign_uid`
WHERE `category_record_mm`.`foreign_uid` = 123;

内容的提问来源于stack exchange，提问作者Fawad Mirzad