Mysql check multiple rows of same id

Question

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.

In your table...the 2nd pack id for Anna is 1057 but 1353 in your results... — Fabien TheSolution
– Fabien TheSolution, Commented Aug 6, 2015 at 18:47

Andrew Mairose · Accepted Answer · 2015-08-06 18:28:16Z

1

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
);

answered Aug 6, 2015 at 18:28

Andrew Mairose

11.1k15 gold badges62 silver badges108 bronze badges

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

2 Comments

Fabien TheSolution Over a year ago

This will not address the part saying "I just want the all the people that could have package ids of (1056 OR 1057) AND (1351 OR 1352 OR 1353)".

Andrew Mairose Over a year ago

Ah, I misunderstood the question.

Fabien TheSolution · Accepted Answer · 2015-08-06 18:43:56Z

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 |

Collectives™ on Stack Overflow

Mysql check multiple rows of same id

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related