CODEWITHSUNDEEP
mysqlsqljoinwhere-clause
user3061527
user3061527

Reputation: 49

Mysql select with multiple condition

I would need help creating a query Table A

+------------+--------------------+-------+
| product_id | name               | price |
+------------+--------------------+-------+
|         13 | Product 13         |     5 |
|         14 | Product 14         |     2 |
|         15 | Product 15         |     3 |
|         16 | Product 16         |     2 |
|         17 | Product 17         |    15 |
+------------+--------------------+-------+

Table B

+----+------------+-------------+
| id | product_id | taxonomy_id |
+----+------------+-------------+
| 10 |         13 |           5 |
| 11 |         13 |           2 |
| 12 |         14 |           3 |
| 13 |         15 |           2 |
| 14 |         16 |          15 |
| 14 |         16 |           5 |
| 14 |         16 |          19 |
| 14 |         16 |          21 |
| 14 |         16 |          18 |
+----+------------+-------------+

my attempt

SELECT *
FROM A
LEFT JOIN B ON B.product_id = A.product_id 
WHERE IF(B.taxonomy_id IN ('5','15'), 
         IF(B.taxonomy_id IN ('2'), 1, 0), 0) = 1
GROUP BY A.product_id

I need it to give me back those results from table A for which it is true

B.taxonomy_id is "5" OR "15" and B.taxonomy_id is "2"

The result would be for this example -> product_id - 13 and I also need to get a number of results SELECT count(*) ... -> return is 1

Upvotes: 0

Views: 11669

Answers (4)

Rutvik Joshi
Rutvik Joshi

Reputation: 107

You can use either join with having clause or intersect function with 2 queries to get the output

13

in your senario. Join with having clause:

select a.product_id from a inner join b on a.product_id = b.product_id group by a.product_id having (SUM (b.taxonomy_id IN (5,15)) and SUM (b.taxonomy_id in (2)));

Intersect with 2 queries:

select a.product_id from a where (a.product_id IN (select product_id from b where b.taxonomy_id = 2))
INTERSECT
select a.product_id from a where (a.product_id IN (select product_id from b where b.taxonomy_id in (5,15)));

For count use something like this which will return

1

as an output:

select COUNT(*) from (select a.product_id from a where (a.product_id IN (select product_id from b where b.taxonomy_id = 2))
INTERSECT
select a.product_id from a where (a.product_id IN (select product_id from b where b.taxonomy_id in (5,15)))) I;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use exists:

select a.*
from a
where exists (select 1
              from b
              where b.product_id = a.product_id and
                    b.taxonomy_id in (5, 15)
             ) and
       exists (select 1
              from b
              where b.product_id = a.product_id and
                    b.taxonomy_id in (2)
             ) ;

If you just wanted the product_ids, then I would recommend aggregation:

select b.product_id
from b
where b.taxonomy_id in (2, 5, 15)
group by b.product_id
having sum( b.taxonomy_id in (5, 15) ) > 0 and
       sum( b.taxonomy_id in (2) ) > 0 ;

Upvotes: 0

Tom Girou
Tom Girou

Reputation: 39

Is it normal that your tables don't have an id column as a unique primary key ?

Anyway, here is what I came across, tell me if it works : 

SELECT table_nameA.product_id
FROM table_nameA
LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
WHERE taxonomy_id = 2 AND table_nameA.product_id IN
    (SELECT table_nameA.product_id
    FROM table_nameA
    LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
    where taxonomy_id = 5 or taxonomy_id = 15
    GROUP BY table_nameA.product_id, taxonomy_id)

Result is : 

| product_id |
|------------|
|         13 |

About your count query, it is exactly the same.

SELECT count(table_nameA.product_id) as Quantity
FROM table_nameA
LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
WHERE taxonomy_id = 2 AND table_nameA.product_id IN
    (SELECT table_nameA.product_id
    FROM table_nameA
    LEFT JOIN table_nameB on table_nameA.product_id = table_nameB.product_id
    where taxonomy_id = 5 or taxonomy_id = 15
    GROUP BY table_nameA.product_id, taxonomy_id)

Result is : 

| Quantity |
|----------|
|        1 |

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Instead of doing filtering in the WHERE clause; you need to do this conditional filtering inside the HAVING clause. You can avoid LEFT JOIN, as the product should have taxonomies for them (2 AND (5 or 15)):

SELECT a.product_id 
FROM tablea a 
JOIN tableb b on b.product_id = a.product_id 
GROUP BY a.product_id 
HAVING SUM(b.taxonomy_id IN (5,15)) 
       AND SUM(b.taxonomy_id = 2)

Result

| product_id |
| ---------- |
| 13         |

View on DB Fiddle

Upvotes: 0

Related Questions