CODEWITHSUNDEEP
mysql
Tom
Tom

Reputation: 622

Mysql check multiple rows of same id

I'll start off with my table:

id    |   name    |   label_id  | package_id
 1        Fred         23742        1056
 2        Fred         23742        1351
 3        Jack         43743        1057
 4        Jack         43743        1352
 5        Bob          13523        1056
 6        Anna         58232        1056
 7        Anna         58232        1057

So each person has their own label id but shared package_ids. I just want the all the people that could have package ids of (1056 OR 1057) AND (1351 OR 1352 OR 1353)

 1        Fred         23742        1056
 2        Fred         23742        1351

 3        Jack         43743        1057
 4        Jack         43743        1352

 6        Anna         58232        1056
 7        Anna         58232        1353

but exclude people that have a single row like bob

 5        Bob          13523        1056

I'll try to elaborate more if needed because its difficult for me to explain.

Upvotes: 0

Views: 146

Answers (2)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE mytable
    (`id` int, `name` varchar(4), `label_id` int, `package_id` int)
;

INSERT INTO mytable
    (`id`, `name`, `label_id`, `package_id`)
VALUES
    (1, 'Fred', 23742, 1056),
    (2, 'Fred', 23742, 1351),
    (3, 'Jack', 43743, 1057),
    (4, 'Jack', 43743, 1352),
    (5, 'Bob', 13523, 1056),
    (6, 'Anna', 58232, 1056),
    (7, 'Anna', 58232, 1353)
;

Query 1:

select *
from mytable
where name in (
    select name
    from (
        select distinct name 
        from mytable
        where package_id in (1056, 1057)
        union all
        select distinct name 
        from mytable
        where package_id in (1351, 1352, 1353)
   ) as result
   group by name
   having count(*) > 1)

Results:

| id | name | label_id | package_id |
|----|------|----------|------------|
|  1 | Fred |    23742 |       1056 |
|  2 | Fred |    23742 |       1351 |
|  3 | Jack |    43743 |       1057 |
|  4 | Jack |    43743 |       1352 |
|  6 | Anna |    58232 |       1056 |
|  7 | Anna |    58232 |       1353 |

Upvotes: 0

Andrew Mairose
Andrew Mairose

Reputation: 11005

I believe you are looking for something like this. This will select all rows where the package_id is in the numbers you listed, and excludes people that only have one row in the table.

select * from <table-name> 
where package_id in (1056, 1057, 1351, 1352, 1343)
and name in (
    select name from <table-name>
    group by name
    having count(*) > 1
);

Upvotes: 1

Related Questions