Simple MySql SELECT

Question

I have a table that assigns workdays to worker-IDs.

Table: workdays       Table: jobs
+------+-------+      +------+-----------+
| id   | days  |      | id   | jobs      |
+------+-------+      +------+-----------+
|    1 |  mon  |      |    1 | cleaning  |
|    1 |  tue  |      |    2 | cooking   |
|    2 |  sat  |      |    3 | driving   |
|    3 |  mon  |      |    4 | cleaning  |
|    3 |  tue  |      |    5 | cooking   |
|    3 |  sat  |      +------+-----------+
|    4 |  wed  |
|    4 |  mon  |
|    5 |  tue  |
+------+-------+

This is the query I use to select the IDs of people who work on any of the days I specify (say Monday and Tuesday):

SELECT * FROM workdays WHERE days IN ( 'mon', 'tue' )

Now I'd like to select those people who work on ALL of the days I specify. How can I do that?

edit: Added second table.

Assuming (id,days) is UNIQUE... GROUP BY id HAVING COUNT(*) = number of items in IN() — Strawberry
– Strawberry, Commented Nov 1, 2013 at 17:15
I wonder if the OP is expecting to return the people that work in ALL of those days and ONLY in those days — Mosty Mostacho
– Mosty Mostacho, Commented Nov 1, 2013 at 17:23

John Woo · Accepted Answer · 2013-11-01 17:14:43Z

4

assuming days is unique for every id.

SELECT id
FROM workdays 
WHERE days IN ('mon', 'tue')
GROUP BY id
HAVING COUNT(*) = 2

otherwise, you need to have DISTINCT in HAVING clause to count only unique values.

HAVING COUNT(DISTINCT days) = 2

answered Nov 1, 2013 at 17:14

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eevaa Over a year ago

Great, thank you! One more thing: Let's say I have another table assigning a jobs to every worker. How could I select all workers available on monday and tuesday AND doing the cleaning for example? I tried "SELECT * FROM workdays NATURAL LEFT JOIN jobs WHERE days IN ('mon, 'tue') AND job IN ('cleaning')", but it doesn't return any results.

eevaa Over a year ago

I added it to my post.

Collectives™ on Stack Overflow

Simple MySql SELECT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related