CODEWITHSUNDEEP
mysql
Cleo Poulin
Cleo Poulin

Reputation: 15

struggling with select with join and where clause

I'm running some SQL through phpmyadmin. I'm trying to do a query with 2 tables. The first xlsws_product has all the product info the second xlsws_category_assn contains two columns one with category id and the other with product id. The category names are in a third table but I don't need them for this.

What I am looking to do is select the rows from the product table that are in the categories with id 210, 218 and 370. This is what I tried so far:

SELECT *
FROM xlsws_product
JOIN xlsws_product_category_assn ON xlsws_product.id = xlsws_product_category_assn.product_id
WHERE 
    xlsws_product_category_assn.category_id = '210' OR '218' OR '370'`

The result for this gave me 24090 rows from a bunch of categories and there should only by a handful of rows in those categories. What's bizarre here is that there are 56474 rows in the product table so I'm not sure how the results are being filtered.

Just for the hell of it I tried limiting my query to just one category id with the following query: 

SELECT *
FROM xlsws_product
JOIN xlsws_product_category_assn ON xlsws_product.id = xlsws_product_category_assn.product_id
WHERE xlsws_product_category_assn.category_id = '210'

This yielded zero rows...

I'm sure there is something simple I am missing but after spending a while searching for a solution I just can't figure it out. Thanks for the help.

Upvotes: 1

Views: 50

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

If you need to find all the data which are in the category '210' OR '218' OR '370'

You can do as

SELECT * FROM 
xlsws_product xp 
JOIN xlsws_product_category_assn xpc ON xp.id = xpc.product_id 
WHERE 
xpc.category_id in (210,218,370)

If you need to find the products which have all the 3 given category you can do as

SELECT * FROM 
xlsws_product xp 
JOIN xlsws_product_category_assn xpc ON xp.id = xpc.product_id 
WHERE 
xpc.category_id in (210,218,370)
group by xp.id having count(*) = 3

Upvotes: 1

Related Questions