mysql - Link to multiple foreign rows from one row -

mysql - Link to multiple foreign rows from one row -

i don't know how called couldn't find answer.

for illustration have next tables:

table products table users +----------+----------+----------+ +----------+----------+ | id | name | cost | | username | products | +----------+----------+----------+ +----------+----------+ | 1 | breadstuff | 1.5 | | james | 1, 2 | +----------+----------+----------+ +----------+----------+ | 2 | cookies | 2.0 | | david | 2, 3 | +----------+----------+----------+ +----------+----------+ | 3 | milk | 1.2 | | paul | 3, 1 | +----------+----------+----------+ +----------+----------+

products.id foreign key users.products.

i name , cost of david's products using mysql query. should homecoming following:

+----------+----------+ | name | cost | +----------+----------+ | cookies | 2.0 | +----------+----------+ | milk | 1.2 | +----------+----------+

the join function best utilize guess, david can have 1 or 100 products. creating 100 columns fit products doesn't sound efficient.

how can realize this?

currently i'm fetching , filter using php, not problem @ moment tables grow guess inefficient.

this solved changing info model.

users +----------+----------+ | id | username | +----------+----------+ | 1 | fred | +----------+----------+ | 2 | john | +----------+----------+ products +----------+----------+----------+ | id | name | cost | +----------+----------+----------+ | 1 | breadstuff | 1.5 | +----------+----------+----------+ | 2 | cookies | 2.0 | +----------+----------+----------+ | 3 | milk | 1.2 | +----------+----------+----------+

and here comes magic: connect 2 tables using 3rd table:

user_procuct_connections +----------+----------+------------+ | id | user_id | product_id | +----------+----------+------------+ | 1 | 1 | 2 | -> fred has cookies +----------+----------+------------+ | 2 | 1 | 3 | -> fred has milk +----------+----------+------------+ | 3 | 2 | 1 | -> john has breadstuff +----------+----------+------------+

if want user able own single product only, can remove id column, create user_id , product_id primary key together.

then when want illustration of freds products just

select * products id in ( select product_id user_procuct_connections user_id = 1 )

mysql sql