Product Table

Attribute Table

In the above table, i want to find a specific product_id with both 'new' and 'blue' attributes(In this situation, expected value is product_id 1)

select product_id form attrbute_table where attr in('new', 'blue') 

did not give me the answer I was looking for

CodePudding user response：

I can propose two solutions:

• We can run 2 sub queries, one for each attribute, and then return only the productID's which are in both result sets.
• We can count the number of line returned which contain "blue" or "new".
  NB: If 2 lines are returned we assume that one is "blue" and one is "new". If your data could return 2 lines with the same PID and both "blue" this query would return a false positive. We could around this with a sub-query with DISTINCT but it would not be any simpler than solution 1.

create table attribute (
  ID int primary key,
  PID int,
  attr VARCHAR(10));

✓

insert into attribute values
(1,1,'new'),
(2,1,'blue'),
(3,1,'car'),
(4,2,'new'),
(5,3,'blue');

✓

SELECT 
  a.PID
FROM
(SELECT PID, attr  FROM attribute WHERE attr ='blue') a
JOIN
(SELECT PID,attr  FROM attribute WHERE attr ='new') b
ON a.PID = b.PID;

| PID |
| --: |
|   1 |

SELECT 
  PID  product_id,
  COUNT(attr) num_found
FROM attribute
WHERE attr = 'blue' or attr = 'new'
GROUP BY PID
HAVING COUNT(attr) = 2;

product_id | num_found
---------: | --------:
         1 |         2

db<>fiddle here

CodePudding user response：

'i want to find a specific product_id with both 'new' and 'blue' attributes'

so you can use and condition

select product_id form attrbute_table where attr = 'new' and attr = 'blue' 

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions. https://www.w3schools.com/sql/sql_in.asp