MySQL Group By Question

Question

I have a table of tickets and need to select a unique list of tickets that a certain user is not associated with. I cannot just say "WHERE user_id != 10" because the GROUP BY statement affects which user_id is returned.

Lastly, I am working with a MySQL database.

SELECT * FROM tickets;
+----+---------+--------+
| id | user_id | ticket |
+----+---------+--------+
|  1 |      10 | T12345 |
|  2 |      11 | T23456 |
|  3 |      12 | T34567 |
|  4 |      10 | T34567 |
|  5 |      13 | T45678 |
+----+---------+--------+

SELECT * FROM tickets WHERE user_id != 10 GROUP BY ticket;
+----+---------+--------+
| id | user_id | ticket |
+----+---------+--------+
|  2 |      11 | T23456 |
|  3 |      12 | T34567 |
|  5 |      13 | T45678 |
+----+---------+--------+

-- Needed result
-- User #10 also worked with ticket T34567,
-- so I do not want it listed in the results
+----+---------+--------+
| id | user_id | ticket |
+----+---------+--------+
|  2 |      11 | T23456 |
|  5 |      13 | T45678 |
+----+---------+--------+

Can someone help me build a query to return my needed results?

Thank you!

Answer 1

This should do the trick:

SELECT *
FROM tickets
WHERE ticket NOT IN (SELECT ticket FROM tickets WHERE user_id = 10)

This selects all the tickets from the ticket table that don't match the tickets that the user (id = 10) has.

The GROUP BY clause is meant to be used (as OrangeDog said) for aggregate queries. So for example, if you were going to find the number of users for a ticket, you would use:

SELECT COUNT(*) AS tcnt, ticket
FROM tickets
GROUP BY ticket

Which would give you:

+------+--------+
| tcnt | ticket |
+------+--------+
|    1 | T12345 |
|    1 | T23456 |
|    2 | T34567 |
|    3 | T45678 |
+------+--------+

Since you're not doing something like that here, it's not necessary.

Answer 2

SELECT *
FROM tickets 
WHERE ticket NOT IN (SELECT ticket FROM tickets WHERE user_id = 10);

GROUP BY is for when you're using aggregate functions (COUNT, SUM, ect.). It usually won't do anything useful if you're not.