MySQL union - alias not visible

Question

The idea is to create a query which will be used as autocomplete but which will move results that have exact keyword right to the top.

First part of the query p1 searches for exact match while the last part p2 searches for any occurence of a substring. Meaning the provider that has "One" in its name will be selected in a first part and the provieder that has "Done" in its name will be selected in a second part.

The problem is that second part selects items from first part too.
Because of that I tried to add an alias to each part of the union and also tried to add additional condition to last part of the union: AND p2.id != p1.id

That won't work with MySQL:

SQL Error (1054): Unknown column 'p1.id' in 'where clause'.

Also tried with HAVING but no luck at all.

---

The question: Is there a way to get such a result in one go?

---

SELECT * FROM (

    (SELECT DISTINCT 
        p1.*, 
        1 AS priority, 
        CASE WHEN parent_id IS NULL THEN 1 ELSE 2 END AS is_parent
    FROM provider p1
    WHERE CONCAT(' ', name, ' ') LIKE '% one %'
    LIMIT 100)
    
    UNION  
    
    (SELECT DISTINCT 
        p2.*, 
        2 AS priority, 
        CASE WHEN parent_id IS NULL THEN 1 ELSE 2 END AS is_parent
    FROM provider p2
    WHERE name LIKE '%one%' AND p2.id != p1.id
    LIMIT 100)

    ORDER BY priority, is_parent
    
) AS t LIMIT 100;

---

Sample data:

ID | Parent ID | Name          
---------------------------
 1 | <NULL> | One dove
 2 |      1 | One play
 3 | <NULL> | Monitor
 4 |      1 | Day one
 5 | <NULL> | Drone
 6 | <NULL> | Screen
 7 | <NULL> | Done with you
 8 | <NULL> | Not done
 9 | <NULL> | All as one

The result expexted:

ID | Parent ID | Name           | Priority | Is parent         
------------------------------------------------------
 1 |    <NULL> | One dove       |        1 |         1
 9 |    <NULL> | All as one     |        1 |         1
 2 |         1 | One play       |        1 |         2
 4 |         1 | Day one        |        1 |         2
 5 |    <NULL> | Drone          |        2 |         1
 7 |    <NULL> | Done with you  |        2 |         1
 8 |    <NULL> | Not done       |        2 |         1

Answer 1

I think you can use the less expensive instr(...) function and select all the rows in one pass.

See example

select *
from(
select *
  ,case when instr(Name,' one ')>0 then 1
        when instr(Name,'one ')=1 then 1
        when instr(Name,' one')=(length(name)-length(' one')+1) then 1
        when instr(Name,'one')>0 then 2
  else 0
  end priority
from test t
)a
where priority>0

ID	Parent_ID	Name	priority
1	null	One dove	1
2	1	One play	1
4	1	Day one	1
5	null	Drone	2
7	null	Done with you	2
8	null	Not done	2
9	null	All as one	1

With test data

ID	Parent_ID	Name
1	null	One dove
2	1	One play
3	null	Monitor
4	1	Day one
5	null	Drone
6	null	Screen
7	null	Done with you
8	null	Not done
9	null	All as one

fiddle