MySql LEFT加入多个相同的ID或名称的表(MySql LEFT join multiple table same with same id or name)

MySql LEFT加入多个相同的ID或名称的表(MySql LEFT join multiple table same with same id or name)

我有一些问题。 用JEFT JOIN sql语句。 我有3个表格：

用户，产品，prod_images。

table.user user_id table.products user_id item_id table.prod_images user_id item_id

当我运行这个查询来获取关系的数据时，它的工作正常。 但只有在prod_images表不为空时才有效。 当prod_images清空sql右连接合并结果，我得到null到products.item_id数组

SELECT products.*, prod_images.*, users.* FROM products LEFT JOIN prod_images ON products.item_id=prod_images.item_id AND prod_images.is_primary = '1' JOIN users ON users.user_id=products.seller_id WHERE products.status = '1' ORDER BY created DESC

如何在table.prod_images为空时使此查询正常工作？

i'm having some problems. with the JEFT JOIN sql statement. i have 3 tables:

user , products, prod_images.

table.user user_id table.products user_id item_id table.prod_images user_id item_id

when i run this query to get data that relation, its work fine. but only work if the prod_images table are not empty. when the prod_images empty the sql right join merge the result and i get null to the products.item_id array

SELECT products.*, prod_images.*, users.* FROM products LEFT JOIN prod_images ON products.item_id=prod_images.item_id AND prod_images.is_primary = '1' JOIN users ON users.user_id=products.seller_id WHERE products.status = '1' ORDER BY created DESC

How can i make this query work fine when the table.prod_images is empty ?

最满意答案

我相信你的查询几乎可以。

它会返回您要求的所有内容，但您应该避免请求products.*, prod_images.*, users.*因为它很容易让我们自己感到困惑。

因此，如果您将此部分至少更改为SELECT products.item_id PRODUCTS_ITEM_ID, products.*, prod_images.*, users.*

另外请注意，当prod_images没有相关记录（因为它是LEFT JOIN ed），但是当users由于INNER JOIN编辑而没有相关记录时，您的查询可能不会返回products.item_id 。 所以我会从将JOIN users更改为LEFT JOIN users 。

PS看看@Reno的评论。 我没有提到这一点。 肯定你应该将ON products.item_id=products.item_id更改为ON products.item_id=prod_images.item_id

I believe your query is almost ok.

And it returns everything you asked for, but you should avoid of requesting products.*, prod_images.*, users.* because it is easy to confuse ourselves.

So if you change this part at least to SELECT products.item_id PRODUCTS_ITEM_ID, products.*, prod_images.*, users.*

And one another note, your query probably returns no products.item_id not when prod_images has no related records (because it LEFT JOINed) but when users has no related records because it is INNER JOINed. So I would start from changing JOIN users to LEFT JOIN users.

PS Look at @Reno comment. I did not mention that. for sure you should change ON products.item_id=products.item_id to ON products.item_id=prod_images.item_id