-1

I want to get the names of the workers that did orders on day x AND day y.

select name
from worker,
     orders
 where worker.workerid = oders.workerid
   and date = x and date = y;

So I want only to get the workers that did orders on both days.

noxus Nexus
  • 83
  • 1
  • 5
  • 2
    Tip of today: Switch to modern, explicit `JOIN` syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. – jarlh Sep 25 '18 at 09:27
  • Hi. If you want things that are tall & things that are short, does that mean you want things that are tall & short? No. Just because a word pops into your head it doesn't mean you should stop thinking. PS This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. Also hits googling 'stackexchange homework'. Show parts you can do. – philipxy Sep 25 '18 at 10:52

5 Answers5

2

in (x,y) will get you the workers that worked in at least one of the 2 days.

While this one here will get you those who worked 2 distinct days.

group by name
having count(distinct date)=2

In total you get what you want

select name
from worker,
     orders
 where worker.workerid = oders.workerid
   and date in (x,y)
group by name
having count(distinct date)=2
kkica
  • 4,034
  • 1
  • 20
  • 40
  • 2
    Wouldn't this fail, if a worker has 2 orders on X and 1 on Y (so 3 in Total?) If so, maybe worth mentioning. Also 2 orders on "X" would match the having-condition? – dognose Sep 25 '18 at 09:49
  • added the distinct – kkica Sep 25 '18 at 09:56
1

Do a GROUP BY. Use HAVING to make sure both dates are there.

select name
from worker
join orders
    on worker.workerid = orders.workerid
where date in (x, y)
group by name
having count(distinct date) >= 2

Note the switch to modern, explicit JOIN syntax!

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Using count distinct at the HAVING is a bad approach... especially if you'er already using group by – Guy Louzon Sep 25 '18 at 09:36
  • I agree completly, by not adding DISTINCT after the group, it overkill... I fixed my own answer – Guy Louzon Sep 25 '18 at 09:38
  • 1
    @GuyL: the `distinct` **is** necessary unless `name, date` is defined as unique in the table. –  Sep 25 '18 at 09:39
  • I didn't say it wan't needed, I just said that this is a wrong use of it... group by and having are enough for this specific case... – Guy Louzon Sep 25 '18 at 09:41
  • @GuyL: no, it's not wrong. It's required. See here for an example: http://rextester.com/JGWSK57293 –  Sep 25 '18 at 09:50
1

Using two exists where clauses...

SELECT name 
FROM worker AS w
WHERE EXISTS (SELECT 1
    FROM orders AS o 
    WHERE w.workerid = o.workerid
    AND date = x)
AND EXISTS (SELECT 1
    FROM orders AS o 
    WHERE w.workerid = o.workerid
    AND date = y)
Barry
  • 3,303
  • 7
  • 23
  • 42
1

The easiest approach is to use 2 partial queries and the postgre Intersect operator: 

select name
from worker,
     orders
 where worker.workerid = oders.workerid
   and date = y
INTERSECT
select name
from worker,
     orders
 where worker.workerid = oders.workerid
   and date = x;

This will leave only worker names, that have been active at X AND y.

http://www.postgresqltutorial.com/postgresql-intersect/

Like the UNION and EXCEPT operators, the PostgreSQL INTERSECT operator combines the result sets of two or more SELECT statements into a single result set. The INTERSECT operator returns any rows that are available in both result set or returned by both queries.

dognose
  • 20,360
  • 9
  • 61
  • 107
  • INTERSECT, nice. Will work well as long as no more dates are added. – jarlh Sep 25 '18 at 09:38
  • @jarlh well, if the query is generated programmatically, you can intersect `n` queries, as long as you don't exceed the max query length. – dognose Sep 25 '18 at 09:39
  • Of course. What about performance? I suppose each added INTERSECT will read the table one more time? – jarlh Sep 25 '18 at 09:45
  • @jarlh That's another Topic. Also it really depends on, if it is required to have more dates (or lets say: an unknown amount of dates) to be added. Not saying this is the best / only solution, it is just the simplest way while keeping "nice readability". – dognose Sep 25 '18 at 09:47
-1

try this:

SELECT name from worker, orders 
WHERE worker.workerid = oders.workerid
    DATE date IN (x, y)
Barry
  • 3,303
  • 7
  • 23
  • 42
Fred Xu
  • 1
  • This is not what Im looking fore because when I use "in" I get also the workers that worked only on day x or on day y but I only need the workers that worked on both days. – noxus Nexus Sep 25 '18 at 09:30