Question 276684 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: How to filter results on columns through a SQL query

I need to know if the following can be resolved with a SQL query and how. I have done it with PHP in different ways but the memory skyrockets and the server takes a long time to return the results. 🙁

  • I have a table called 'parts' with many rows (but many).
  • Each of the rows has the columns part1, part2 and part3, all with different values.

Example:

+---------+-------+-------+
| part1   | part2 | part3 | <-- Column Name
+---------+-------+-------+
| chamber | wheel | door  | <-- Value
+---------+-------+-------+

On the other hand I have a list of three values that come from an external source:

$list = ['mirror', 'seat', 'door'];

I need to select the rows whose columns contain all those values, regardless of the order. An example of a valid row would be:

+-------+--------+-------+
| part1 | part2  | part3 |
+-------+--------+-------+
| door  | mirror | seat  |
+-------+--------+-------+

Sometimes the external list doesn’t have 3 values, just 1 or 2:

$list = ['antenna'];

… Then the valid rows would be those that contain that value, for example:

+--------+--------+---------+
| part1  | part2  | part3   |
+--------+--------+---------+
| engine | mirror | antenna |
+--------+--------+---------+
  • Additional info: In each row, the columns do not have repeated values, so this feature can be ignored in the query.
  • I need to know how to make a query for SELECT and another for COUNT.
  • It is necessary that the result be resolved through a MySQL query, without using PHP for filtering.
Tags:

2

Answers


  1. 0

    Possible solution.

    CREATE TABLE parts
SELECT 'chamber' part1, 'wheel' part2, 'door' part3 UNION ALL
SELECT 'door', 'mirror', 'seat' UNION ALL
SELECT 'engine', 'mirror', 'antenna';
SELECT * FROM parts;
    part1 part2 part3
    chamber wheel door
    door mirror seat
    engine mirror antenna 
    SELECT parts.*
FROM parts
CROSS JOIN (
  SELECT 'mirror' UNION
  SELECT 'seat' UNION
  SELECT 'door'
) criteria (value) 
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)
    part1 part2 part3
    door mirror seat 
    SELECT parts.*
FROM parts
CROSS JOIN (
  SELECT 'mirror' UNION
  SELECT 'antenna'
) criteria (value) 
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)
    part1 part2 part3
    engine mirror antenna 
    SELECT parts.*
FROM parts
CROSS JOIN (
  SELECT 'antenna'
) criteria (value) 
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)
    part1 part2 part3
    engine mirror antenna

    fiddle

    If you don’t want to convert your criteria array to subquery text (which must be concatenated into) then you may convert it to JSON array form, provide it to MySQL and parse to separate values in static subquery using JSON_TABLE().

    Login or Signup to reply.
  2. 0

    An alternative approach, building on @CBroe’s suggestion. If you can sort the criteria before you insert them into the SQL statement, then this might work for you.

    First normalise the parts list, then reassemble a string of parts in sorted order, separated and wrapped by commas:

    SELECT id, CONCAT(',', GROUP_CONCAT(part ORDER BY part), ',') AS p
FROM (
    SELECT id, part1 AS part FROM parts
    UNION ALL SELECT id, part2 FROM parts
    UNION ALL SELECT id, part3 FROM parts
) partslist
GROUP BY id

    Finally wrap in a overall query that applies the criteria and extracts the matching rows:

    SELECT parts.*
FROM (
    SELECT id, CONCAT(',', GROUP_CONCAT(part ORDER BY part), ',') AS p
    FROM (
        SELECT id, part1 AS part FROM parts
        UNION ALL SELECT id, part2 FROM parts
        UNION ALL SELECT id, part3 FROM parts
    ) partslist
    GROUP BY id
) partsjoined
INNER JOIN parts ON partsjoined.id = parts.id
WHERE partsjoined.p LIKE CONCAT('%,', CONCAT_WS('%,%', ?, ?, ?), ',%')

    (reduce the number of ? parameters as appropriate)

    Again, this assumes you’re able to sort the criteria parameters before binding them to the statement.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search