1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

help with mysql query

Discussion in 'MySQL' started by Mariam, Dec 24, 2009.

  1. Mariam

    Mariam New Member

    Joined:
    Sep 9, 2007
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  2. technica

    technica New Member

    Joined:
    Dec 15, 2007
    Messages:
    107
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    Select * from table1 where Text like '%Finance Manager%'
    Union
    Select * from table2 where Text like '%Finance%' OR Text like '%Manager%'


    Hope this helps you
     
  3. Mariam

    Mariam New Member

    Joined:
    Sep 9, 2007
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    0
    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"
     
  4. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    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:
    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%'
     
    shabbir likes this.
  5. Mariam

    Mariam New Member

    Joined:
    Sep 9, 2007
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    0
    Thanks a lot!
     

Share This Page