MySQL query returns unwanted rows when retrieving rows based on specific tag combinations

Question

I am running PHP + MySQL on my Windows 8 PC. 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 ║ ║ 9999 ║ 101 ║ 6 ║ ║ 9999 ║ 102 ║ 8 ║ ╚═════════╩══════╩═════╝ 

I have input 101 , 102 . I want the result to be as follows:

 2,5 6,8 

The query will look for a combination of 101,102 and returns accurate combination with another lot number. Along with this, I want to avoid duplicate strings. Here 1111 and 5555 have the same tags with the same lot numbers before the tag (exactly the same combinations with the same lots), 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 . In short, I want products with the exact combination of 101, 102 , and I don't want products with any additional tags, and I don't want anything with missing tags.

I have a request as shown below:

 SELECT DISTINCT GROUP_CONCAT(m.lot) FROM mytable m WHERE m.tag IN (101,102) AND (SELECT COUNT(m.product) FROM mytable m2 WHERE m.product = m2.product)>1 GROUP BY m.product; 

which returns;

 2,5 2 5,2 1,3 6,8 

How can i fix this? Is there a better way to accomplish what I'm looking for?

Here is the fiddle starting with http://sqlfiddle.com/#!9/d11d6

Answer 1

I suggest using a simple connection for this:

 SELECT DISTINCT a.lot, b.lot FROM mytable a INNER JOIN mytable b ON b.product = a.product AND NOT EXISTS (SELECT * FROM product WHERE tag NOT IN (a.tag, b.tag)) WHERE a.tag = 101 and b.tag = 102 

Answer 2

I am wondering if this does what you want:

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

This seems to return the results you want. This would be more efficient with the where clause:

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

Answer 3

When performing the product group, the HAVING clause is clearly looking for ONLY those products that have both 101 and 102 and NO OTHER. Once each product is qualified, it includes concatenated batches. If the other has the same concatenation, only one set is returned. Because of your duplicate entries of the same tag 101 inside the product, I can only do if one of them is found regardless of its duplication, therefore, MAX () for 101 and 102 are both = 1, respectively.

 select DISTINCT group_concat( DISTINCT m.lot ORDER BY m.lot ASC SEPARATOR ',' ) from myTable m group by m.product having MAX( case when m.tag = '101' then 1 else 0 end ) = 1 AND MAX( case when m.tag = '102' then 1 else 0 end ) = 1 AND sum( case when m.tag in ( '101', '102' ) then 0 else 1 end ) = 0; 

And if you want to see how each individual product is compared, delete the HAVING clause to see their respective values.

 select m.product, group_concat( DISTINCT m.lot ORDER BY m.lot ASC SEPARATOR ',' ), sum( case when m.tag in ( '101', '102' ) then 1 else 0 end ) as WantTags, sum( case when m.tag in ( '101', '102' ) then 0 else 1 end ) as OtherTags from myTable m group by m.product 

I created an SQLFiddle instance for you. A few minor things. I changed the column names to "tag1", "lot1".

 select DISTINCT group_concat( DISTINCT m.lot1 ORDER BY m.lot1 ASC SEPARATOR ',' ) from myTableXYZ m group by m.product having MAX( case when m.tag1 = 101 then 1 else 0 end ) = 1 AND MAX( case when m.tag1 = 102 then 1 else 0 end ) = 1 AND sum( case when m.tag1 in ( 101, 102 ) then 0 else 1 end ) = 0; 

So my query runs against your SQLFiddle and has the results

 2,5 1,3,5 6,8