Part of my schema is as pictured
我的架构的一部分如图所示
I have two junction tables, one of which maps Items to Categories and the second which maps what items are "featured" for a particular category.
我有两个联结表,其中一个将项目映射到类别,第二个映射表示特定类别的“特色”项目。
What I need to do now is get all items that are in a particular category (not difficult), but I also need to have a column "featured" that would display if the item is featured for that category (NULL if not featured).
我现在需要做的是获取特定类别中的所有项目(并不困难),但我还需要一个“特色”列,如果项目是该类别的特色,则显示该列(如果没有特色,则为NULL)。
I've tried various combinations of LEFT JOIN for example
我尝试过LEFT JOIN的各种组合
SELECT i.*, category.name, category_feat_item.item_id AS featured
FROM item AS i
INNER JOIN item_category ON i.id = item_category.item_id
INNER JOIN category ON category.id = item_category.category_id AND category.id =1
LEFT OUTER JOIN category_feat_item ON i.id = category_feat_item.item_id
ORDER BY featured DESC
but I'm stumped.
但我很难过。
1 个解决方案
#1
1
Looks almost ok but you need to assign the category_id in the left outer join as well. Otherwise you get all featured items of the item:
看起来几乎没问题但你需要在左外连接中分配category_id。否则,您将获得该项目的所有特色项目:
SELECT i.*, category.name, category_feat_item.item_id AS featured
FROM item AS i
INNER JOIN item_category ON i.id = item_category.item_id
INNER JOIN category ON category.id = item_category.category_id AND category.id =1
LEFT OUTER JOIN category_feat_item ON i.id = category_feat_item.item_id AND category_feat_item.category_id = 1
ORDER BY featured DESC
#1
1
Looks almost ok but you need to assign the category_id in the left outer join as well. Otherwise you get all featured items of the item:
看起来几乎没问题但你需要在左外连接中分配category_id。否则,您将获得该项目的所有特色项目:
SELECT i.*, category.name, category_feat_item.item_id AS featured
FROM item AS i
INNER JOIN item_category ON i.id = item_category.item_id
INNER JOIN category ON category.id = item_category.category_id AND category.id =1
LEFT OUTER JOIN category_feat_item ON i.id = category_feat_item.item_id AND category_feat_item.category_id = 1
ORDER BY featured DESC