How to separate overlapping results of WHERE clauses in SQL?-CodePudding

I'm searching for multiple people by finding matching names in a table using multiple WHERE clause criteria and then sort the results based on the order in which the names appear in the WHERE clause, something like this:

SELECT * FROM table
WHERE table.NAME LIKE '%Alice%' OR
      table.NAME LIKE '%Bob%' OR
      table.NAME LIKE '%Charlie%'
ORDER BY CASE
         WHEN table.NAME LIKE '%Alice%' THEN 1
         WHEN table.NAME LIKE '%Bob%' THEN 2
         WHEN table.NAME LIKE '%Charlie%' THEN 3
         ELSE 4
         END

My problem with this is that if I accidentally search for the same name twice (having overlapping results) mySQL only returns the record once which messes up my data ordering and later post processing. For example the following query returns results for Alice, Bob and Charlie in order whereas I would need it to return results for Alice, Bob, Alice, Charlie in order even if the results for the two Alices are the same.

SELECT * FROM table
WHERE table.NAME LIKE '%Alice%' OR
      table.NAME LIKE '%Bob%' OR
      table.NAME LIKE '%Alice%' OR
      table.NAME LIKE '%Charlie%'
ORDER BY CASE
         WHEN table.NAME LIKE '%Alice%' THEN 1
         WHEN table.NAME LIKE '%Bob%' THEN 2
         WHEN table.NAME LIKE '%Alice%' THEN 3
         WHEN table.NAME LIKE '%Charlie%' THEN 4
         ELSE 5
         END

Is it even possible in SQL to separate the results of each sub query in the WHERE clause? For context, I need to return results into a dataframe in Python using pandas.read_sql(). I considered launching separate queries for each name in Python, but it would be really inefficient to do 100 queries instead of one.

CodePudding user response：

SQL is a set-processing language. Your FROM whatever WHERE whatever clauses return a set of rows. Your ORDER BY clause sorts that result set into the order you specify.

You are looking for a way for some rows to appear multiple times in the result set. That requires either, as you mention, multiple queries, or it requires UNION ALL.

Something like this may do the trick.

SELECT *
  FROM (
            SELECT *, 1 order_position FROM table WHERE name LIKE '%Alice%'
            UNION ALL
            SELECT *, 2 order_position FROM table WHERE name LIKE '%Bob%'
            UNION ALL
            SELECT *, 3 order_position FROM table WHERE name LIKE '%Alice%'
       ) r
 ORDER BY order_position

CodePudding user response：

You could put the search criteria in a virtual or real table, and join, then order by an ordering column

SELECT t.*
  FROM [table] t
  JOIN (VALUES
    (1, '%Alice%'),
    (2, '%Bob%'),
    (3, '%Alice%')
  ) v(order_position, search)
    ON t.name LIKE v.search
ORDER BY v.order_position