Search in more than one table - give result as one column.

Question

Hey stackoverflow - This is my first question here.

I have 2 tables in my mySQLdb.

[tab_artist]
|id|artist|count  

[tab_songtitle]
|id|songtitle|artistId

Im trying to select from both tab_artist.artist and tab_songtitle.songtitle as suggest where searchclause = m

I have tried this

SELECT artist, songtitle AS suggest 
FROM tab_artist, tab_songtitle 
WHERE artist LIKE('m%') OR songtitle LIKE('m%')

But this gives me 2 columns, artist and suggest

if the search is met i need artist to give me e.g. metallica.. but only once - but in songtitles i need all titles starting with met.

Hope this makes sence to the right expert :)

Answer 1

A union should do it:

select artist    AS suggest from tab_artist    WHERE artist    LIKE 'm%'
union all
select songtitle AS suggest from tab_songtitle WHERE songtitle LIKE 'm%'

For this case, I would use union all so you won't get duplicates removed, but you may want this if, for example, Metallica has a self-titled album and you only want the word to appear once. In that case, union would be more appropriate.

Answer 2

you want to use SQL UNION

select id, artist, count FROM table1
UNION
select id, title, artistid from table2

It basically concats the two result sets vertically. There are some restrictions and modifications, but generally thats all you need to do.

Answer 3

Use a UNION ALL to concatenate the results from the artist and song tables.

SELECT 'Artist' AS type, id, artist AS suggest
FROM tab_artist
WHERE artist LIKE 'm%'
UNION ALL
SELECT 'Song', id, songtitle
FROM tab_songtitle
WHERE songtitle LIKE 'm%'

Don't use UNION as some are suggesting as that will do a completely unnecessary (and expensive) DISTINCT on the query results.

Answer 4

You need a join:

Select artist, songtitle from tab_artist inner join tab_songtitle on tab_songtitle.artistID = tab_artist.ID where artist LIKe ('m%') OR songtitle like ('m%')