Little Child Little Child - 6 months ago 7
SQL Question

SQL Join involving 3 tables, how to?

SQL newbie here.

So we have 3 tables:

categories(cat_id,name);
products(prod_id,name);
relationships(prod_id,cat_id);


It is a one-to-many relationship.

So, given a category name say "Books". How do I find all the products that come under books?

As an example,

categories(1,Books);
categories(2,Phones);
products(302,Sherlock Holmes);
relationships(302,1);

Answer

You have to join tables on related columns and specify WHERE clause to select all records where category name = 'Books'

SELECT p.*
FROM categories c
JOIN relationships r ON c.cat_id = r.cat_id
JOIN products p ON r.prod_id = p.prod_id
WHERE c.name = 'Books'
Comments