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

As you have generated group_concat() which is grouping the records you want but it is just like string. In you example when you are passing value 101,102 your query does group_concat() and return result of 101,102 and compare. But when you are passing data 102,101 the query same does the group_concat() of 101,102 and compare it with 102,101 so as this is string, you can not get the proper result. — Keyur Mistry
– Keyur Mistry, Commented Jan 21, 2016 at 9:03
you are explicitely ordering the tags before concatenating them to a string and then comparing the strings, so the value you compare with has to be ordered as well — Kai Iskratsch
– Kai Iskratsch, Commented Jan 21, 2016 at 9:07
Possible duplicate of How to retrieve unique rows based on column value combinations? — Strawberry
– Strawberry, Commented Jan 21, 2016 at 10:16

ScaisEdge · Accepted Answer · 2016-01-21 10:16:24Z

0

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 . " ; ";

edited Jan 21, 2016 at 10:16

answered Jan 21, 2016 at 10:06

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

5 Comments

Strawberry Over a year ago

If you see DISTINCT and GROUP BY in the same query, at the very least alarm bells should start ringing.

Strawberry Over a year ago

That's making it worse :-(

ScaisEdge Over a year ago

@Strawberry yes.. thanks i have just copied the query by OP i was looking for ordering solution..anyway i have upate then answer..

ScaisEdge Over a year ago

@Strawberry the query however worl correctly with group by ..,. could be is what the OP need.. .. This don't change the senso of my suggestion..that is if you compare an ordered result you nedd an ordered matching value..

Strawberry Over a year ago

I suspect that the problem is that the OP doesn't really know what they need. When they figure that out, I think we might be able to provide a more authoritative answer.

Kickstart · Accepted Answer · 2016-01-21 13:32:26Z

0

While I would tend to use a combination of php and mysql, I thought I would have a play and see if I could come up with a pure SQL version of this:-

SELECT DISTINCT GROUP_CONCAT(lot ORDER BY lot)
FROM `mytable`
GROUP BY product
HAVING COUNT(DISTINCT IF(FIND_IN_SET(tag, '101,102'), tag, NULL)) = 1 + LENGTH('101,102') - LENGTH(REPLACE('101,102', ',', ''))
AND COUNT(tag) = 1 + LENGTH('101,102') - LENGTH(REPLACE('101,102', ',', ''));

This will not care which order the comma separated list of values is in. But is not going to be efficient!

answered Jan 21, 2016 at 13:32

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related