MySQL query returns duplicate rows

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 the input 101,102. I want the output like;

2,5
6,8

I have a query like;

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

It returns;

2,5
2,5
6,8

Instead of two 2,5, I want just one, avoiding duplicate rows. How can I do this?

Here is the fiddle http://sqlfiddle.com/#!9/7a78bb/1/0

Possible duplicate of MySQL query returns unwanted rows on fetching rows based on specific tag combinations — CodeGodie
– CodeGodie, Commented Jan 19, 2016 at 21:22
@GiorgosBetsos it worked. Please post it as answer so that I can accept — Alfred
– Alfred, Commented Jan 19, 2016 at 21:22

Gurwinder Singh · Accepted Answer · 2016-12-24 17:46:41Z

7

If you want distinct then

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

edited Dec 24, 2016 at 17:46

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

answered Jan 19, 2016 at 21:21

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

shivani parmar · Accepted Answer · 2016-05-26 06:14:22Z

0

You need to just use DISTINCT to Eliminate Duplicates

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

answered May 26, 2016 at 6:14

shivani parmar

3342 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL query returns duplicate rows

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related