MySQL: SELECT DISTINCT from multiple tables

Question

I have database with several tables in it. For sake of example I will simplify it and try to explain in detail.

One table is called Products, another called Brands, and the last one is called Categories.

As you might guessed each Product can have only one Category assigned. Also, each Product can have only one Brand assigned. These are the relationships.

So, on one page I have links with Brands as parameter that I pass to details page where I list all Products from specific Brand. On that page I also have filter by Categories that filters only products from selected Brand by that filter.

E.g. Sony -> then filter applied from drop down is dSLR -> Results of Sony brand dSLR camera products.

What I would like is to have that Categories filtered so that if Brand doesnt have specific category it doesnt even show on that drop down filter.

E.g. Categories are predefined as: dSLR, Video, Cell phone, Shoes

Sony have loads of Products, but doesnt make shoes, so I would like to have it excluded from that filter list on details page.

Hope you understood what I want here...

Any suggestion is more than welcomed :)

Simon · Accepted Answer · 2011-09-27 07:59:44Z

1

Well, you should be able to JOIN the three tables like so:

SELECT DISTINCT c.category_id, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN brands b ON p.brand_id = b.brand_id
WHERE b.brand_name='Sony';

and then display the products like so:

SELECT p.product_id, p.product_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN brands b ON p.brand_id = b.brand_id
WHERE b.brand_name='Sony' AND c.category_id='1234';

As danishgoel already noted, make sure you have the proper indices on your tables, since this is a quite expensive query.

answered Sep 27, 2011 at 7:59

Simon

4,6002 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

danishgoel · Accepted Answer · 2011-09-27 07:41:03Z

0

One way is to select the list of distinct categories from the products of a specific brand.

You can do it like:

SELECT DISTINCT(cat_id) FROM PRODUCTS WHERE brand_id = 'sony'

Now you have only those categories that are available for sony products

The above query might be a little slow to run on each page request. You can speed it up by creating an INDEX on brand_id, cat_id

edited Sep 27, 2011 at 7:41

answered Sep 27, 2011 at 7:32

danishgoel

3,6451 gold badge21 silver badges32 bronze badges

1 Comment

user966456 Over a year ago

Maybe I wasnt clear enough. I need sql query which will populate that drop down box for filter. Categories-> cat_id, cat_name so with that I populate drop down list, and when you select drop down, you get filtered Sony brand by some category...

Collectives™ on Stack Overflow

MySQL: SELECT DISTINCT from multiple tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related