MySQL where with multiple conditions

Question

Table events

|  PkID  |  StartDate  |   Title  |
|    1   | 2020-08-21  | Event #1 |
|    2   | 2020-08-21  | Event #2 |
|    3   | 2020-08-22  | Event #3 |

Table eventcategories

|  EventID  | CategoryID |
|     1     |     40     |
|     1     |    144     |
|     2     |    144     |
|     3     |     40     |
|     3     |    146     |

I want to display events which have category 40 and categories 144 or 146.

In my example Event #1 and Event #3

I try

SELECT e.Title
        FROM events e
            LEFT JOIN eventcategories ec ON (ec.EventID = e.PkID)
        WHERE ec.CategoryID = 40 AND ec.CategoryID IN (144,146) AND e.StartDate >= "2020-08-07"

but no results

Related: https://stackoverflow.com/questions/16704290/how-to-return-rows-that-have-the-same-column-values-in-mysql?lq=1 — Barmar, Aug 07 '20 at 16:34

score 0 · Answer 1 · edited Aug 07 '20 at 16:27

0

The where condition is applied row wise. So for any one row your both conditions: ec.CategoryID = 40 AND ec.CategoryID IN (144,146) cannot be true simultaneously. This is the reason your query is not working

I see this approach working:

Fetch event ids from eventcategories where category id = 40
Fetch event ids from eventcategories where category id in (144,146)
Inner Join 1 and 2 to get event ids that satisfy both
Join with events to get Title

edited Aug 07 '20 at 16:27

Dharman

30,962
25
85
135

answered Aug 07 '20 at 16:22

Rishabh Sharma

747
5
9

Could just combine all the possible categories in the IN: WHERE ec.CategoryID IN (40, 144,146). Or use an OR: WHERE e.StartDate >= "2020-08-07" AND (ec.CategoryID = 40 OR ec.CategoryID IN (144,146)) – John Mitchell Aug 07 '20 at 16:37
That would be incorrect as it would include EventId 2 in the resultset but the expected result set contains EventId 1 and 3 only. – Rishabh Sharma Aug 08 '20 at 04:13

forpas · Accepted Answer · 2020-08-07T16:47:57.473

You can do it with an INNER join and setting the conditions in the HAVING clause:

SELECT e.Title
FROM events e INNER JOIN eventcategories ec 
ON ec.EventID = e.PkID
WHERE ec.CategoryID IN (40, 144, 146) AND e.StartDate >= '2020-08-07'
GROUP BY e.PkID, e.Title
HAVING SUM(ec.CategoryID = 40) 
   AND MAX(ec.CategoryID) IN (144, 146)

The above HAVING clause may work for your sample data but it may not be scalable.
Another, more general option would be:

HAVING SUM(ec.CategoryID = 40) 
   AND SUM(ec.CategoryID IN (144, 146))

See the demo.
Results:

> | Title    |
> | :------- |
> | Event #1 |
> | Event #3 |

MySQL where with multiple conditions

2 Answers2