Mysql Query Returns Unwanted Rows On Fetching Rows Based On Specific Tag Combinations

I run PHP+MySQL in my Windows 8 PC. I have a table mytable like below; ╔═════════╦══════╦═════╗ ║ product ║ tag ║ lot ║ ╠

Solution 1:

I suggest using simple join for this:

SELECTDISTINCT a.lot, b.lot
FROM mytable a
    INNERJOIN mytable b ON b.product = a.product ANDNOTEXISTS (SELECT*FROM product WHERE tag NOTIN (a.tag, b.tag))
WHERE a.tag =101and b.tag =102

Solution 2:

I'm wondering if this does what you want:

select group_concat(lot orderby lot)
from mytable
groupby product
having group_concat(tag orderby tag) = '101,102';

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

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

Solution 3:

By doing group by product, and HAVING clause is explicitly looking for ONLY those products that have both the 101 and 102 and NO OTHER. Once each product is qualified, it's concatenated lots are included. If another has same concatenation, only one set will be returned. Because of your duplicate entries of the same tag 101 within a product, I only care if one IS found regardless of it being duplicated hence the MAX() for 101 and 102 both = 1 respectively.

selectDISTINCT
      group_concat( DISTINCT m.lot ORDERBY m.lot ASC SEPARATOR ','  )
   from
      myTable m
   groupby
      m.product
   havingMAX( casewhen m.tag ='101'then1else0end ) =1ANDMAX( casewhen m.tag ='102'then1else0end ) =1ANDsum( casewhen m.tag in ( '101', '102' ) then0else1end ) =0;

And if you want to see how each individual product compares, remove the HAVING clause to see their respective values

select 
      m.product,
      group_concat( DISTINCT m.lot ORDERBY m.lot ASC SEPARATOR ','  ),
      sum( casewhen m.tag in ( '101', '102' ) then1else0end ) as WantTags,
      sum( casewhen m.tag in ( '101', '102' ) then0else1end ) as OtherTags
   from
      myTable m
   groupby
      m.product

I created a SQLFiddle instance for you. Few minor things. I changed the column names to "tag1", "lot1".

selectDISTINCT
      group_concat( DISTINCT m.lot1 ORDERBY m.lot1 ASC SEPARATOR ','  )
   from
      myTableXYZ m
   groupby
      m.product
   havingMAX( casewhen m.tag1 =101then1else0end ) =1ANDMAX( casewhen m.tag1 =102then1else0end ) =1ANDsum( casewhen m.tag1 in ( 101, 102 ) then0else1end ) =0;

So my query runs against your SQLFiddle and has the results

2,5
1,3,5
6,8