MySQL: how to select ID's with certain values in column

Question

    +-----------+--------------+----------+--------+
    | ID        | Package      |   Date   | Amount |
    +-----------+--------------+----------+--------+
    |     1     |      2       |2000/09/10|  2000  |
    +-----------+--------------+----------+--------+
    |     1     |      4       |2002/09/20|  3000  |
    +-----------+--------------+----------+--------+
    |     1     |      3       |2012/10/01|  5000  |
    +-----------+--------------+----------+--------+
    |     1     |      4       |2012/10/01|  1000  |
    +-----------+--------------+----------+--------+
    |     2     |      2       |2012/10/01|  2000  |
    +-----------+--------------+----------+--------+
    |     2     |      4       |2012/10/01|  5000  |
    +-----------+--------------+----------+--------+
    |     3     |      2       |2012/10/01|  3000  |
    +-----------+--------------+----------+--------+
    |     3     |      3       |2012/10/01|  5000  |
    +-----------+--------------+----------+--------+

For example, I have this table called 'Packages'.

Every ID is given one package on multiple dates which contain (amount) of items. When an ID receives more than 1 package, there are multiple rows with the same ID.

I would like to select all ID's that have received both Package 2 and 4. In this case, I want mySQL to return ID 1 and 2, since ID 3 only has Package 2 but not 4.

I have used this query:

select
ID
from Packages
where Package = 2 and Package = 4

But when I use this, i get "No Results".

Is there a way to do this? Thanks in advance.

score 2 · Accepted Answer · answered May 14 '18 at 10:59

Here is one canonical way to do this:

SELECT ID
FROM Packages
WHERE Package IN (2, 4)
GROUP BY ID
HAVING COUNT(DISTINCT Package) = 2;

The basic idea is to restrict to only rows having the two packages of interest, then aggregate by ID and assert that the distinct remaining package count is 2. This implies that your requirement is being met.

score 0 · Answer 2 · answered May 14 '18 at 11:01

0

select ID from Packages where Package in(2,4)

answered May 14 '18 at 11:01

Mayur

11
6

1

kk .. i think its wrong – Mayur May 14 '18 at 11:03
I think this would some rows which should not be part of correct result, like row with id 3 according to given table in ques. – Gaurav Singh May 14 '18 at 19:09

score 0 · Answer 3 · answered May 14 '18 at 11:03

You need to do a double select. The way you wrote your code, you select rows where the value is simultaneously 2 and 4. That cannot happen hence the empty result set you're getting back.

Try a nested select e.g.

Select id from packages where id in (select id, package from packages where package='2') and  package ='4'

You should also be able to use intersect. See https://www.techonthenet.com/sql/intersect.php

More simply you can do

Select id from packages where package in (2,4)

score 0 · Answer 4 · answered May 14 '18 at 11:06

0

Use:

select * from packages
where id in (select distinct id
from  (select distinct id from packages where package=2) a
 join (select distinct id from packages where package=4) b
   on (a.id = b.id))

answered May 14 '18 at 11:06

score 0 · Answer 5 · edited May 14 '18 at 11:30

0

SELECT id FROM Packages WHERE Package = 2  
INTERSECT  
SELECT id FROM Packages WHERE Package = 4;

(Semicolon only at the end)
I hope this should work

edited May 14 '18 at 11:30

nacho

5,280
2
25
34

answered May 14 '18 at 11:20

Gaurav Singh

456
6
17

MySQL: how to select ID's with certain values in column

5 Answers5