Marvin Calderon Marvin Calderon -4 years ago 133
MySQL Question

How to select all values of a field that has specific values in another field?

I have a table in which the field 'id_px' can have some repeated values because it has a different value in the field 'id_category' and I want to retrieve all values on 'id_px' that has the exact values that I pass. For example:

SELECT id_px FROM my_table WHERE id_category = 1 **AND** id_category = 32;


I found a way that retrieves me the result that I want:

SELECT id_px
FROM my_table
GROUP BY id_px
HAVING SUM(id_category NOT IN (1,32)) = 0
AND SUM(id_category = 1) = 1
AND SUM(id_category = 32) = 1


but I want to use it whitout the HAVING clause because I have to use some UNION clause.

Hope you can help me, thanks in advance.

Answer Source

I suggest 3 ways:

1- Using group by and searching in id_px:

SELECT tbl.id_px FROM
  (SELECT id_px , CONCAT('-,' , GROUP_CONCAT(id_category),',') as gr
   FROM my_table
   GROUP BY id_px
  ) as tbl
  WHERE
      INSTR( tbl.gr , ',1,'  )>0
  AND INSTR( tbl.gr , ',32,' )>0

2- Selecting id_px by join

SELECT a.id_px
FROM my_table as a
LEFT JOIN my_table as b
ON a.id_px = b.id_px
WHERE a.id_category = 1 AND b.id_category = 32
GROUP BY a.id_px

3- 3rd one:

SELECT tbl.id_px FROM
  (SELECT id_px ,
    SUM(id_category NOT IN (1,32)) as b ,
    SUM(id_category = 1) + SUM(id_category = 32) as t
  FROM my_table
  GROUP BY id_px) as tbl
WHERE tbl.b = 0 AND tbl.t = 2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download