How to get unique rows based on combinations of column values?

Question

I have a mytable table as shown 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 input 101 , 102 . I want the result to be as follows:

 2,5 3,5 

which means that in the table he will look for combinations 101,102 and returns exact combinations with another lot number . Along with this, I want to avoid duplicate strings. Here 1111 and 5555 have the same tags with the same batch numbers before tag s, so I want only one line instead of two lines. Despite the fact that 8888 has tags 101 and 102 with different lot s, it cannot be considered for listing, since it includes tag 103 . This means that I want products with the exact combination of 101, 102 . In short, I don't want products with any additional tags, and I don't want anything with missing tags.

How can i achieve this?

Answer 1

EDIT: answer modified for updated question

NOTE: request 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 a group to achieve this.

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

it is also possible using various, but you look into it. I cannot remember if different things are possible in several columns. I think this will work too ...

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

Answer 2

What about a simple simple SELECT query with an IN clause and GROUP By how?

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

SQLFiddle

Answer 3

Try the following:

 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 understood from your question, I think that you need the same product of this tag having diff lot

NOTE. I am not sure about the CONCAT function, since I did not practice it.

Answer 4

I tried this to avoid duplication and to group columns and columns together. Try changing to get the desired output.

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

Solution according to the 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 

Contact: DISTINCT , GROUP_CONCAT

Ref: MySQL query returns duplicate rows