Searching Multiple Tables (SQL)

Question

I need to be able to have an SQL query that searches my database using a simple search. Here is what my tables look like right now:

Table artists
--------------
id
name

Table albums
-------------
id
artistID
name

Table songs
------------
id
albumID
name

How would I go about doing this? Here are some SQL queries that I have tried, but my issue is that it is returning a lot of data. For instance, if I search for an artist like Snoop Dogg, it is returning a row for each album and song that he has even if they don't contain his name.

SELECT * FROM artist,album,songs WHERE artist.name LIKE '%snoop%' OR albums.name LIKE '%snoop%' OR songs.name LIKE '%snoop%';

Edit:

Here is a sample database;

artists
-----------
1 | Snoop Dogg
2 | Linkin Park

albums
--------
1 | artist=1 | Boom
2 | artist=2 | ThisIsIt

songs
--------
1 | album=1 | First
2 | album=2 | Second Linkin
3 | album=2 | Third
4 | album=1 | Fourth

So I want a search for "snoop" to return just the artist "Snoop Dogg". But then a search like "Linkin" to return the artist and the song.

First, joins are missing. Second, what do you want to see if you type in "snoop" if not his songs? — Nikola Markovinović
– Nikola Markovinović, Commented Mar 21, 2012 at 23:57
@safarov Well each song is linked to an album and that is linked to an artist. — Flipper
– Flipper, Commented Mar 21, 2012 at 23:58

Nikola Markovinović · Accepted Answer · 2012-03-22 00:08:17Z

5

This query will retrieve matching artists, songs and albums. First column gives a clue about origin of the data, second is id and the last is the name.

select 'Artists' OriginatingTable, id, name
  from artists
 where name like '%snoop%'
union all
select 'Albums', id, name
  from albums
 where name like '%snoop%'
union all
select 'Songs', id, name
  from songs
 where name like '%snoop%'

answered Mar 22, 2012 at 0:08

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tadeck · Accepted Answer · 2012-03-22 07:53:51Z

5

The following uses UNION, thus avoids JOINs:

(
    SELECT
        'artist' as `type`,
        `artists`.`id` as `id`,
        `artists`.`name` as `name`
    FROM `artists`
    WHERE
        `artists`.`name` LIKE '%snoop%'
)
UNION
(
    SELECT
        'album' as `type`,
        `albums`.`id` as `id`,
        `albums`.`name` as `name`
    FROM `albums`
    WHERE
        `albums`.`name` LIKE '%snoop%'
)
UNION
(
    SELECT
        'song' as `type`,
        `songs`.`id` as `id`,
        `songs`.`name` as `name`
    FROM `songs`
    WHERE
        `songs`.`name` LIKE '%snoop%'
)

edited Mar 22, 2012 at 7:53

answered Mar 22, 2012 at 0:09

Tadeck

138k28 gold badges155 silver badges201 bronze badges

3 Comments

Flipper Over a year ago

I got this error when trying this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE artists.name LIKE '%snoop' ) UNION ( SELECT ' at line 6

Flipper Over a year ago

And also, how does type differentiate from OriginatingTable in Nikola's answer?

Tadeck Over a year ago

@Flipper: I missed FROM statement, this probably caused an error for you :) I looked at Nikola's answer: his originating table does what my type does, there are not many differences however. There is one inconsistency (the column is called OriginatingTable, but contains a string that is different than table name, is not lowercase), plus my solution allows you to modify partial queries thanks to brackets. If you wish to eg. add ORDER BY etc. within his solution, then add brackets like in my answer. Anyway, as I said, his answer is very similar to mine, but he was 1.5 minute faster ;)

Jordan Mack · Accepted Answer · 2012-03-22 00:17:18Z

3

You need to join all your data together so it becomes one big data set that will allow you to query against all fields at once. To do this you want to use a left join which is the most optimal for this scenario.

SELECT *
FROM songs
LEFT JOIN albums
ON songs.albumID = albums.id
LEFT JOIN artists
ON albums.artistID = artists.id
WHERE artist.name LIKE '%snoop%'
OR albums.name LIKE '%snoop%'
OR songs.name LIKE '%snoop%';

A union could also be used, but this may not give you the results you want. A left join will give you a row for every song that matches the artist, album, or song. A union will give you a row for each song where where it matches the song name, a row for each album where it matches the album name, and a row for each artist where is matches the artist name.

edited Mar 22, 2012 at 0:17

answered Mar 21, 2012 at 23:59

Jordan Mack

8,8437 gold badges33 silver badges30 bronze badges

Comments

JotaBe · Accepted Answer · 2012-03-22 00:13:32Z

0

You should select from each table and return and union of the results, but remember that all the select must return the same number and kind of columns.

SELECT 'Artist' as kind, id as artistId, name, null as albumId, null as SongId
FROM artist WHERE artist.name LIKE '%snoop%' 
 union
SELECT 'Album', artistId, name, id, null FROM album WHERE albums.name LIKE '%snoop%'
 union
SELECT 'Song', albums.artistId, songs.name, songs.albumId, songs.id 
 FROM songs inner join albums on songs.albumId = albums.id
 WHERE songs.name LIKE '%snoop%'

This way you avoid getting a combination of all the tables, and you also have all the available info in each row.

answered Mar 22, 2012 at 0:13

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

Comments

safarov · Accepted Answer · 2012-03-21 23:59:40Z

-1

This query search on artist name, song name and album name

SELECT artist.name,albums.name,songs.name 
FROM songs 
LEFT JOIN album ON album.id = song.albumID
LEFT JOIN artists ON album.artistID = artists.id
WHERE 
artist.name LIKE '%snoop%' OR albums.name LIKE '%snoop%' OR songs.name LIKE '%snoop%'

answered Mar 21, 2012 at 23:59

safarov

7,7942 gold badges38 silver badges52 bronze badges

Collectives™ on Stack Overflow

Searching Multiple Tables (SQL)

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related