Join Question (Retrieve multiple rows)

[websponge]

I want to have a supplier table, with ID, NAME, CONTACT, PHONE, etc,

In my main table, a record can have more than one of these suppliers, what is the best way to select multiple rows from the supplier table if they relate to a record in the main table?

I was going to have a column in the main table SUPPID and have "1,3,4" as an entry so it selects record 1, 3 and 4 from the supplier table, but obviously this wont work!

any guidance is appreciated.

[Szabi Zsoldos]

First please create a sqlfiddle with your tables and data so we can help you with the query.

[diafol]

You need a link table, eg.

Supplier:  ID, NAME, CONTACT, PHONE
Product: ProdID, ProdNAME
ProductSuppliers: PSID, ProdID, ID

WHere ProdID and ID in the ProductSuppliers are Foreign Keys. You can force a constraint or not.

To retrieve all the suppliers of a particular product:

SELECT Product.ProdNAME, GROUP_CONCAT(Supplier.NAME) 
    FROM ProductSuppliers AS PS 
        INNER JOIN Product 
            ON Product.ProdID = PS.ProdID 
        INNER JOIN Supplier 
            ON Supplier.ID = PS.SupplierID 
    WHERE Product.ProdID = $product_id 
        GROUP BY Product.ProdID

Off top of my head - so could be wrong.

[websponge]

Szabi, this is at planning stage, I havent created the tables yet.

Im familiar with joins, the issue is, how can i have a entry that relate to 2 or 3 records from another table, one product may have more than 1 supplier, so I need to put an entry in the product row that says "I use supplier 2,5 and 6"

Thanks