CODEWITHSUNDEEP
mysqlselectdistinctdistinct-values
user966456
user966456

Reputation: 3

MySQL: SELECT DISTINCT from multiple tables

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 :)

Upvotes: 0

Views: 3463

Answers (2)

Simon
Simon

Reputation: 4485

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.

Upvotes: 1

danishgoel
danishgoel

Reputation: 3645

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

Upvotes: 0

Related Questions