php - Why this MySQL query returns result only if input is in a particular order?

Question

I have a table mytable like below;

╔═════════╦══════╦═════╗
║ product ║ tag  ║ lot ║
╠═════════╬══════╬═════╣
║ 1111    ║ 101  ║ 2   ║ 
║ 1111    ║ 102  ║ 5   ║ 
║ 2222    ║ 103  ║ 6   ║ 
║ 3333    ║ 104  ║ 2   ║  
║ 4444    ║ 101  ║ 2   ║ 
║ 5555    ║ 101  ║ 2   ║ 
║ 5555    ║ 102  ║ 5   ║ 
║ 6666    ║ 102  ║ 2   ║ 
║ 6666    ║ 103  ║ 5   ║
║ 7777    ║ 101  ║ 2   ║ 
║ 7777    ║ 102  ║ 5   ║ 
║ 7777    ║ 103  ║ 6   ║ 
║ 8888    ║ 101  ║ 1   ║ 
║ 8888    ║ 102  ║ 3   ║ 
║ 8888    ║ 103  ║ 5   ║ 
║ 9999    ║ 101  ║ 6   ║ 
║ 9999    ║ 102  ║ 8   ║
╚═════════╩══════╩═════╝

I have a query like;

select distinct group_concat(lot order by lot)
from `mytable`
group by product
having group_concat(tag order by tag) = '101,102';

Which is suppose to give me an output like;

2,5
6,8

The query will look for combinations 101,102, and returns the exact same combinations with different lot number. Along with this, I want to avoid duplicate rows. Here 1111 and 5555 has same tags with same corresponding lot numbers to tags (exact same combinations with same lots), so I want only one row instead of 2 rows. Even though, 8888 has tags 101 and 102 with different lots, it cannot be considered for listing , since it includes tag 103 in addition. In short, I want products with exact 101, 102 combination, and I dont want products with any extra tags, and i dont want anything with missing tags.

The code works fine. But there is a problem. If i give input 101,102, the query works fine. But if I give 102,101 as input, then I get no rows, but I want to get the exact result as in the previous condition, ignoring the order in which the tag numbers are given. Also, sometimes there can be more than two numbers as input like 101,102,103, 101,102,103,104 etc.

How can i do this? Here is a fiddle http://sqlfiddle.com/#!9/7a78bb/11/0

Answer 1

If you like also a PHP solution.... a possible solution is order the combition via php and formatting the value in a sequence of value in string ..

$tmp = explode(',', '101,102,103,104');
sort($tmp);
$myComb = implode(',',$tmp);

$sql="select distinct group_concat(lot order by lot)
from `mytable`
having group_concat(tag order by tag) = " . myComb . " ; ";