Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   help with mysql query (http://www.go4expert.com/forums/help-mysql-query-t20495/)

Mariam 24Dec2009 14:16

help with mysql query
 
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 24Dec2009 14:18

Re: help with mysql query
 
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 24Dec2009 15:40

Re: help with mysql query
 
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 25Dec2009 17:37

Re: help with mysql query
 
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 25Dec2009 18:10

Re: help with mysql query
 
Thanks a lot!


All times are GMT +5.5. The time now is 00:04.