What is optimal MySQL statement to select rows from same table that have some ids but not others?

Question

We need to select all segments_id, em_id from the following table mytable that have a segments_id of 5 or 8 but not 1 or 7. To be clearer after comments made below, the result will be a set that includes all rows of segments_id, em_id where em_id has segments_id of 5 or 8 BUT not 1 or 7.

 +-------------+-------+
 | segments_id | em_id |
 +-------------+-------+
 |    1        |   8   |
 |    1        |  17   |
 |    5        |   2   |
 |    5        |   4   |
 |    5        |   5   |
 |    5        |  16   |
 |    5        |  17   |
 |    7        |   4   |
 |    7        |   5   |
 |    7        |   8   |
 |    7        |  16   |
 |    8        |   4   |
 |    8        |   6   |
 |    8        |   8   |
 |    8        |  18   |
 |    18       |   6   |
 |    18       |  99   |
 +-------------+-------+

The result should be:

 +-------------+-------+
 | segments_id | em_id |
 +-------------+-------+
 |    5        |   2   |
 |    8        |   6   |
 |    8        |  16   |
 +-------------+-------+

We need to avoid using IN clause because this can scale to millions of rows.

I understand this will involve a join on itself and/or a subquery but I'm not seeing it. I saw this post Stackoverflow: Selecting rows from a table that have the same value for one field but cannot see the solution.

Answer 1

Using aggregation:

SELECT segments_id,em_id   -- GROUP_CONCAT(segments_id) AS segments_ids
FROM mytable
GROUP BY em_id
HAVING SUM(segments_id IN (8,5)) > 0
   AND SUM(segments_id IN (1,7)) = 0;

SqlFiddleDemo

Output:

╔══════════════╦═══════╗
║ segments_id  ║ em_id ║
╠══════════════╬═══════╣
║           5  ║     2 ║
║           8  ║     6 ║
║           8  ║    18 ║
╚══════════════╩═══════╝

Answer 2

Not sure what you are asking for here. You tell us you want a select all but the results that you posted do not show all records that satisfy your conditions. Please elaborate on the conditions if you meant to exclude some records where segments_id = 5 or 8 and not 1 or 7.

SELECT segments_id,em_id 
FROM mytable 
WHERE ( segments_id = 5 OR segments_id = 8 ) 
AND segments_id != 1 
AND segments_id != 7;

This will work!

Answer 3

You can use a LEFT JOIN to eliminate all em_id which has a segment_id that is 1 OR 7:

SELECT m.segments_id, m.em_id
FROM myTable as m
LEFT JOIN (SELECT * FROM myTable WHERE (segments_id=1 OR segments_id=7)) as n
ON m.em_id=n.em_id
WHERE n.segments_id IS NULL
GROUP BY m.em_id;

See SQLfiddle here.

If it is 5 AND 8 you want to keep and all the rest should be eliminated, then you can tweak one of your WHERE clauses like this:

SELECT m.segments_id, m.em_id
FROM myTable as m
LEFT JOIN (SELECT * FROM myTable WHERE NOT (segments_id=5 OR segments_id=8)) as n
ON m.em_id=n.em_id
WHERE n.segments_id IS NULL
GROUP BY m.em_id;

SQLfiddle for the second query is here.

And if it is only 5 AND 8 you want to keep and 1 AND 7 you want to eliminate, you can use this (however in this case, the answer given by @lad2025 might be a better choice):

SELECT m.segments_id, m.em_id
FROM myTable as m
LEFT JOIN (SELECT * FROM myTable WHERE (segments_id=1 OR segments_id=7)) as n
ON m.em_id=n.em_id
WHERE (n.segments_id IS NULL AND (m.segments_id=5 OR m.segments_id=8))
GROUP BY m.em_id;

Pls. check third SQLfiddle with an improved set here.