1

i have two tables: songs and singers

and this my query:

    Select * FROM (
    SELECT
    idx,
    name,
    IDsinger,
    permission,
    (name LIKE '%XXX%') As relevancy,
    'table1' As t
    FROM `songs`
   where
   isActive<>'0' AND name LIKE '%XXX%'
UNION
    SELECT
        idx,
        name,
        CreationDate,
        permission,
        (name LIKE '%XXX%') As relevancy,
        'table2' As t
        FROM `singers`
        WHERE isActive<>'0' AND name LIKE '%XXX%'
) AS X
order by relevancy LIMIT 10

The problem is if i write "akon lonely" is not found result.

But if i write "akon" or "lonely" is found result.

And i would love suggestions for improving query..

Thanks

user1490014
  • 43
  • 3
  • 4
    Please post some sample data and the expected results. – Barmar Oct 29 '13 at 05:51
  • I don't understand the `relevancy` fields. Since your queries only return rows that match the `LIKE` expression, they'll all have `relevancy = 1`. – Barmar Oct 29 '13 at 05:52
  • Are there rows in either of the tables that contain `akon lonely` in the `name` field? `LIKE` is not a full-text search, it does an exact match except for the `%` wildcards. So it won't find a field that contains just one of the words. – Barmar Oct 29 '13 at 05:54

1 Answers1

0

Your result is correct. AKON is a singer and Lonely is the song. Neither the Song or the Singer is called Akon lonely.

Sounds like you are trying to do a more complicated form of searching like contains.

http://technet.microsoft.com/en-us/library/ms187787.aspx

You could try

name like '%' + @xxx + '%' or @xxx like '%' + name + '%'
Thomas Harris
  • 434
  • 3
  • 14