SELECT from 2 differents tables-CodePudding

I would like to select all possible brands for different products where the fk_category_id is for example equal to "2".

produits :

id  titre   fk_category_id   fk_marque_id  is_active  is_delete
1   Swoke        2               1            1           0
2   Café         2               2            1           0
3   Fraise       2               3            1           0
4   Fruits       2               4            1           0

manufacturers :

id  name
1   Swoke
2   Liqua
3   Alfaliquid
4   TJuice
5   otherBrands

I already tried a lot of things and for example this request :

SELECT m.name, m.id, p.fk_category_id
FROM produits p
  INNER JOIN manufacturers m
WHERE p.fk_category_id = 2
  AND p.is_active = 1
  AND p.fk_marque_id = m.id
  AND p.is_delete = 0;

But it doesn't works.

The expected result is :

result :

id  name
1   Swoke
2   Liqua
3   Alfaliquid
4   TJuice

It's the same as the table "manufacturers" but I have to sort by fk_category_id because I only want the brand with the fk_category = 2.

So if someone could explain me or help me to understand how to solve my "problem" ? Thanks you in advance, I continue my research by my side :).

If you need something else i can give you anything.

CodePudding user response：

I think what you need to do is have a condition on your join, to say how the data from the 2 table should join together.

On the assumption that fk_marque_id is your reference in produits to an item in manufacturers (assumed from looking at your where clause), your sql could look like this:

SELECT 
    p.id, p.titre, m.name, m.id, p.fk_category_id
FROM 
    produits p
    INNER JOIN manufacturers m ON p.fk_marque_id = m.id
WHERE p.fk_category_id = 2
AND p.is_active = 1
AND p.is_delete = 0;

The naming convention of your fields in produits is a little weird however, if one of the the FK fields is a link to an ID in manufacturers. You'd normally expect to see something like FK_manufacturers_Id so it's clear that this column is the reference to that field (Id) in that table (manufacturers)

CodePudding user response：

If you are just looking to join products and manufacturers table where fk_category_id = 2, it can be done something like this

SELECT m.id, m.name
FROM manufacturers m
  INNER JOIN produits p 
ON m.id = p.fk_marque_id
WHERE p.fk_category_id = 2
AND p.is_active = 1
AND p.is_delete = 0;