Use both operation (or) with (and) on the same field in SQL ( not different fields)

Question

I have three tables, many to many relationship

the tables will be like the following if I insert data

I want to get the product name where (propertyValue='ios' or propertyValue = 'android') and (propertyValue='black')

In another way, we can say

I want to get the product name where (propertyid='2' or propertyid = '4') and (propertyid='3')

I tried second way and there is a SQL:

Select DISTINCT(p.productname),p.productid
from Products p
left join Product_propertyvalue ppv on p.productid=ppv.productid
where ( ppv.propertyvalueid=2 or ppv.propertyvalueid=4) and ( ppv.propertyvalueid=3 )

`Select DISTINCT(p.productname),p.productid` -> `DISTINCT` is not a function, At first seight it seams you are trying to get PostgreSQL `DISTINCT ON(productname), *` results or more easy said only keep the *"first"* record where productname duplicates .. Which you can simulate on MySQL which i wrote a [answer](https://stackoverflow.com/questions/53868870/how-to-use-distinct-on-with-mysql-using-activerecord/53869691#53869691) about in the past. — Raymond Nijland, Nov 10 '19 at 17:02
.. if that does not help you see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Nov 10 '19 at 17:03

score 1 · Answer 1 · answered Nov 10 '19 at 17:05

You can use having:

select p.productname, p.productid
from Products p left join
     Product_propertyvalue ppv 
     on p.productid = ppv.productid
group by p.productname, p.productid
having sum( ppv.propertyvalueid in (2, 4) ) > 0 and
       sum( ppv.propertyvalueid = 3 ) > 0;

The sum() conditions check that each property is there for a given product.

Use both operation (or) with (and) on the same field in SQL ( not different fields)

1 Answers1