SQL query across multiple tables

Question

I have only a very basic knowledge of SQL, so pls do forgive if this is a stupid Q. I hunted the internet but could not get an answer.

I have three tables:

title

id int <- PK

artist_id int

album_id int

desc varchar

artist

arist_id int <- FK

desc varchar

album

album_id int <- FK

desc varchar

title data

1, 1, 1, "Give me everything"

1, 2, 2, "More"

1, 3, 3, "What makes you beautiful"

artist data

1, "Pitbull"

2, "Usher"

3, "One Direction"

album data

1, "Planet Pit"

2, "More"

3, "Up All Night"

If I want to do a search of one table I do like so:

select * from artist where desc like '%direction%';

My objective is to do a search across three tables. The search SQL query should return rows for "title" table where the search criteria has been met in the "desc" field of any of the three tables using the artist_id and album_id fields (which are the linking keys).

My brain is totally muddled today. If the Q is not clear, pls do ask for clarity.

possible duplicate of Simple SQL Select from 2 Tables (What is a Join?) — joran
– joran, Commented Jun 15, 2012 at 3:01

aF. · Accepted Answer · 2012-06-08 08:24:44Z

3

You need to make a inner join between tables like this:

select t.* from title t
inner join artist ar on ar.artist_id = t.artist_id
inner join album al on al.album_id = t.album_id
where (t.desc like '%direction%' or ar.desc like '%direction%' or al.desc like '%direction%')

answered Jun 8, 2012 at 8:24

aF.

67k45 gold badges141 silver badges201 bronze badges

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

1 Comment

sphere4a Over a year ago

Just back from weekend, so sorry for the delay. Wow! Tried your query and it just worked straight off. Thx aF.

Collectives™ on Stack Overflow

SQL query across multiple tables

title

artist

album

title data

artist data

album data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

title

artist

album

title data

artist data

album data

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related