CODEWITHSUNDEEP
phpmysql
Tùng Nguyễn
Tùng Nguyễn

Reputation: 103

Select unique value from table has null columns

I have a detail_product table.

the product_id, category_id, color_id and size_id are foreign key for the table product, category, color and sizerespectively

id | product_id | category_id | color_id | size_id
---------------------------------------------------
1  |     1      |      1      |   null   |   null
2  |     1      |      2      |   null   |   null
3  |     1      |     null    |    1     |   null
4  |     1      |     null    |   null   |    2
5  |     2      |      2      |   null   |   null
6  |     2      |     null    |    2     |   null
7  |     3      |      1      |   null   |   null
8  |     3      |      2      |   null   |   null
9  |     3      |     null    |    1     |   null
10 |     3      |     null    |    2     |   null

How I can select the product which satisfies conditions. I got stuck when select because of the null value in these columns. For example, I want to select all products which belong to category 1 and category 2 and have color is 1 then which sql statement should write? (In that example my expected value is that product_id return 1 and 3).

Does anyway to skip these null columns or some statements to solve my problem?

I have tried this but it not work:

SELECT detail_product.product_id 
FROM  `detail_product` 
WHERE  detail_product.category_id = 1 
AND    detail_product.category_id = 2 
AND    detail_product.color_id = 1

Upvotes: 0

Views: 55

Answers (2)

FanoFN
FanoFN

Reputation: 7114

SELECT product_id , GROUP_CONCAT(category_id) catid, GROUP_CONCAT(color_id) colid
FROM detail_product
GROUP BY product_id
HAVING (1 IN (catid) OR 2 IN (catid)) 
       AND 1 IN (colid) ;

Fiddle: https://www.db-fiddle.com/f/r5q7YZVgnERKDPfnK6zNZN/1

Upvotes: 1

Akina
Akina

Reputation: 42612

SELECT product_id 
FROM detail_product
GROUP BY product_id 
HAVING FIND_IN_SET(1, GROUP_CONCAT(category_id)) -- category_id=1 must exist
   AND FIND_IN_SET(2, GROUP_CONCAT(category_id)) -- category_id=2 must exist
   AND FIND_IN_SET(1, GROUP_CONCAT(color_id))    -- color_id=1 must exist

Upvotes: 2

Related Questions