Mysql select with multiple condition

Question

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

the reason why you should always have a primary/unique key when using innoDB .. without primary/unique key you are more or less down grading InnoDB 's multithreaded performance to MyISAM which locks.. — Raymond Nijland
– Raymond Nijland, Commented Sep 5, 2019 at 8:43
your current where clause will only yield results whose taxonomy_id is not in [5, 15, 2] because once it goes true in B.taxonomy_id IN ('5', '15') it goes straight to B.taxonomy_id IN ('2') which will yield false, which leads to 0. I suggest shortening it into B.taxonomy_id IN ('2', '5', 15) — alimbaronia
– alimbaronia, Commented Sep 5, 2019 at 8:44

Tom Girou · Accepted Answer · 2019-09-05 08:37:58Z

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 |

Madhur Bhaiya · Accepted Answer · 2019-09-05 08:04:15Z

0

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

answered Sep 5, 2019 at 8:04

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

5 Comments

user3061527 Over a year ago

The problem is when I want to get -> SELECT COUNT(a.product) ... return is 2

Madhur Bhaiya Over a year ago

@user3061527 why are you doing select count(..) ? That is not required as per your original problem statement. Please explain further, because it seems you want to do something totally different that your original query.

user3061527 Over a year ago

I want the original query but also the query to get the count

Madhur Bhaiya Over a year ago

@user3061527 add expected result in tabular format (like they way you have given the table data) into the question. Then only some clarity would appear.

Madhur Bhaiya Over a year ago

@user3061527 you cannot get count in the same query (unless we use a subquery(Derived Tables and windowing functions). But the good thing is that you can access the num_rows member of mysqli object to get the count indirectly, while your main query gives you all the product_id in separate rows.

Gordon Linoff · Accepted Answer · 2019-09-05 11:29:18Z

0

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 ;

answered Sep 5, 2019 at 11:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Rutvik Joshi · Accepted Answer · 2019-09-06 06:02:37Z

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;

Collectives™ on Stack Overflow

Mysql select with multiple condition

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related