help with mysql query

Mariam's Avatar, Join Date: Sep 2007
Go4Expert Member
Suppose I have 2 tables with ID, and Text

table1
ID Text
1 Finance Manager
2 Accountant
3 Office Manager

table2
ID Text
1 Sales Manager
2 Import manager
3 Finance Manager

I write in search field Finance Manager, the query result must bring first all maches "Finance Manager" then ("Finance" and "Manager") from Table1 and Table2
technica's Avatar, Join Date: Dec 2007
CoderByNature
Select * from table1 where Text like '%Finance Manager%'
Union
Select * from table2 where Text like '%Finance%' OR Text like '%Manager%'


Hope this helps you
Mariam's Avatar, Join Date: Sep 2007
Go4Expert Member
That is not what I want.

From Table1, Table2 must bring results that maches to "Finance Manager"
then
From Table1, Table2 must bring results that maches to "Finance" or "Manager"
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
then you have to modify the query this way

Let us spit this query into parts. And check the statements carefully to understand.

First From Table1 bring results that maches to "Finance Manager"
Select * from table1 where Text like '%Finance Manager%'

Then From Table2 bring results that maches to "Finance Manager"
Here the table is changing not the criteria
Select * from table2 where Text like '%Finance Manager%'

Then From Table1 bring results that maches to "Finance" or "Manager"
Select * from table1 where Text like '%Finance%' OR Text like '%Manager%'

Then From Table2 bring results that maches to "Finance" or "Manager"
Again only the table is changing
Select * from table2 where Text like '%Finance%' OR Text like '%Manager%'

Then Bring all these data together.
Use UNION statement to join two results. But this will work only if the no of columns in both the results being joined are equal

So the Final Solution would be as below:

Code: sql
SELECT * FROM table1 WHERE Text LIKE '%Finance Manager%'
UNION
SELECT * FROM table2 WHERE Text LIKE '%Finance Manager%'
UNION
SELECT * FROM table1 WHERE Text LIKE '%Finance%' OR Text LIKE '%Manager%'
UNION
SELECT * FROM table2 WHERE Text LIKE '%Finance%' OR Text LIKE '%Manager%'
Mariam's Avatar, Join Date: Sep 2007
Go4Expert Member
Thanks a lot!