How to retrieve unique rows based on column value combinations?

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   ║ 
╚═════════╩══════╩═════╝

I have the input 101,102. I want the output like;

2,5
3,5

which means, in the table, it 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, 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. Which means, I want products with exact 101, 102 combination. In short, I dont want products with any extra tags, and i dont want anything with missing tags.

How can I achieve this?

"so I want to avoid duplicate rows. How can I do this?" - auto_increment. — user3119231
– user3119231, Commented Jan 19, 2016 at 8:40
Please provide some more examples on what results you want and what you don't want. — Gerald Schneider
– Gerald Schneider, Commented Jan 19, 2016 at 8:44
i think without duplicate check it should be something like this: select product, tag, lot from mytable m where exist (select 1 from mytable m1 where m1.tag = '101') and exist (select 1 from mytable m2 where m2.tag = '102') and than he need remove duplicates and product info and transform into one row for every product. — user1516873
– user1516873, Commented Jan 19, 2016 at 8:50

ZubaiR · Accepted Answer · 2016-01-19 20:12:52Z

1

EDIT: Answer modified for updated question

NOTE: query not tested

SELECT GROUP_CONCAT(lot ORDER BY lot ASC SEPARATOR ',') 
from mytable 
having count(*) = 2 
  and GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ' ') = '101 102' 
group by product

old answer

you can use group by to achieve this.

select tag, product from mytable where tag in (101,102) group by tag, product

this may also be possible using distinct, but you have take a look into it. i cant remember if distinct is possible in multiple columns. I think this will work too...

select distinct tag, product from mytable where tag in (101,102)

edited Jan 19, 2016 at 20:12

answered Jan 19, 2016 at 8:47

ZubaiR

6054 silver badges7 bronze badges

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

1 Comment

Alfred Over a year ago

can u make a sql fiddle?

Kamaldeep singh Bhatia · Accepted Answer · 2016-01-19 09:05:21Z

0

Try this:

SELECT DISTINCT(CONCAT(firstTable.tag, " - ", firstTable.lot, secondTable.tag, " - ", secondTable.lot))
    (SELECT tag,lot FROM mytable WHERE tag = 101) AS firstTable INNER JOIN
    (SELECT tag,lot FROM mytable WHERE tag = 102) AS secondTable
     ON firstTable.product = secondTable.product
    WHERE firstTable.lot <> secondTable.lot

As I got from your question I think you need same product of given tag having diff lot

NOTE: I am not sure about CONCAT function as I have not practiced it.

edited Jan 19, 2016 at 9:05

answered Jan 19, 2016 at 8:59

Kamaldeep singh Bhatia

7326 silver badges19 bronze badges

6 Comments

Alfred Over a year ago

I cant adopt (SELECT tag,lot FROM mytable WHERE tag = 101) since there will be lot more combinations coming and think will be difficult on the stage.

Kamaldeep singh Bhatia Over a year ago

these (101 102) will be variables and if you have more combinations then just give some examples for that

Kamaldeep singh Bhatia Over a year ago

Your combinations of tag will be of two or more?

Alfred Over a year ago

I mean (SELECT tag,lot FROM mytable WHERE tag = 101) will repeat for every tags. I want to avoid that. I am looking for a more simpler code.

Alfred Over a year ago

There can be n number of combinations

|

Mohd Abdul Mujib · Accepted Answer · 2016-01-19 09:06:41Z

0

How about just a simple SELECT query with IN clause and GROUP By both?

SELECT * FROM mytable
WHERE tag IN (101, 102)
GROUP BY tag, lot;

SQLFiddle

edited Jan 19, 2016 at 9:06

answered Jan 19, 2016 at 8:45

Mohd Abdul Mujib

14.2k8 gold badges73 silver badges93 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:17:59Z

0

I have tried this one it avoids duplicate and group the tab and lot columns together. Try to modify to get required output

SELECT DISTINCT GROUP_CONCAT(CONCAT(tag," - ",lot)) FROM mytable 
WHERE tag IN (101,102) GROUP BY product ;

Solution as per new update:

SELECT DISTINCT GROUP_CONCAT(m.lot) FROM mytable m
GROUP BY m.product HAVING GROUP_CONCAT(m.tag ORDER BY m.tag) = '101,102';

Output:

2,5

Refer: DISTINCT, GROUP_CONCAT

Reference: MySQL query returns duplicate rows

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Jan 19, 2016 at 9:33

TSKSwamy

2254 silver badges22 bronze badges

2 Comments

TSKSwamy Over a year ago

you have the exact solution for your problem in the link provided by @CodeGodie

TSKSwamy Over a year ago

@blasteralfredΨ question is also yours only. Any way you got the solution and me also learnt from that :-)

Collectives™ on Stack Overflow

How to retrieve unique rows based on column value combinations?

4 Answers 4

1 Comment

6 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related