LEFT JOIN with more fields in the right table

Question

I have a problem! My shops table contains 4 rows, each containing a shop name, ID and other data relevant to that shop.

I also have a "bind" table where I select which product categories should be visible in which shops. Naturally the bind table will contain more rows... I would like to select all the shops from the shops table, and join only the rows in the right table, the bind table, if the shop ID is the same.

SELECT * , b.categoryId as bindCategory
FROM shops

LEFT JOIN bind_shop_category AS b
ON shops.shopId = b.shopId

If I do it like that, I get all the binds, with their corresponding shop data, not just the shops and their corresponding bind data. :P

This is very confusing, and I'm not sure I formulated the question 100%.

Uhm... expected data:

  shopId          shopName         bind.categoryId
    1               shop1
    2               shop2            category1
    3               shop3            category1
    4               shop4

Answer 1

try this:

SELECT S.* , B.categoryId as bindCategory
FROM shops S
   LEFT JOIN bind_shop_category B
       ON B.shopId = S.shopId

Answer 2

Hmm. Possibly you mean:

SELECT shops.*, b.categoryId as bindCategory
FROM shops
LEFT JOIN bind_shop_category AS b
ON shops.shopId = b.shopId

(or, the way I'd write it:)

SELECT shops.*, b.categoryId as bindCategory
FROM shops
LEFT JOIN bind_shop_category AS b
USING (shopId)

Answer 3

After the post was updated. You want the following.

SELECT s.ShopId, s.ShopName , b.categoryId as bindCategory
FROM shops
    LEFT OUTER JOIN bind_shop_category AS b
         ON (shops.shopId = b.shopId)
ORDER BY s.ShopId

Answer 4

Your "left" table is the "bind" table and the "right" table is the "shops" table, as you have written your query. You have specified a left join on the two tables, meaning that, you get all records that match in the same way and in addition, you get an extra record for each unmatched record in the left table of the join.

You need to either change the type of join you're doing, the left/right-ness of the tables, or both.

Answer 5

If you want to simply list all the categories for each shop in a single row, you can use GROUP_CONCAT

SELECT s.shopId, s.shopName, GROUP_CONCAT(b.categoryId)
FROM shops s
LEFT JOIN bind_shop_category AS b ON (s.shopId = b.shopId)
GROUP BY s.shopId

Output will be something like this...

1               shop1            NULL
2               shop2            category1,category2
3               shop3            category1

Change the LEFT JOIN to an INNER JOIN to only show shops with at least one category.