sql - MySQL: select list of battles, not shared between 2 users -


i need select list of "battle_id", first "user_id" have, not shared second "user_id",

battles (table structure):

id     user_id     battle_id 0         1           44 1         1           55 2         1           66 3         2           44 4         2           77 5         3           88 6         3           99 7         4           44 8         4           55 9         4           66

sample of input , output:

example 1: input [user_id = 1, user_id = 2] output => 55,66 example 2: input [user_id = 1, user_id = 3] output => 44,55,66 example 3: input [user_id = 1, user_id = 4] output => null

thanks,

sample data:

create table t     (`id` int, `user_id` int, `battle_id` int) ;  insert t     (`id`, `user_id`, `battle_id`) values     (0, 1, 44),     (1, 1, 55),     (2, 1, 66),     (3, 2, 44),     (4, 2, 77),     (5, 3, 88),     (6, 3, 99),     (7, 4, 44),     (8, 4, 55),     (9, 4, 66) ;

query:

select battle_id t group battle_id having sum(user_id = 1) >= 1 , sum(user_id = 2) = 0

result:

| battle_id | |-----------| |        55 | |        66 |

explanation:

you don't need self join suggested in comments. in 1 go.
user_id = 'whatever' inside sum() function in having clause returns either true or false, meaning 1 or 0.


-