SQL query - Simple (ish!)

Dr Pepper

Hi,

Ok, short as I can:
I have a simple table called 'track' with fields: id, artist, title, genreid, subgenreid
I also have a table called genre with fields: id, name

I want a query that will return a list of tracks with their genre and sub-genre names. Yes, it's that simple... Or is it?

Here's what I have (works fine but only shows the genre, not subgenre):
select track.id, artist, title, genre.name from track, genre where track.genreid=genre.id;

I know a 'linking' table called 'trackgenre' would be a better option but changing the table structure is not really an option at this stage! So, can it be done with the existing tables?

Thanks!

(It's one of those ones that's very hard to google!)

hobochris

Is subgenreid the primary key and a subgenre table?

http://www.w3schools.com/sql/sql_join.asp

that should help you learn what you need to do.

Dr Pepper

Thanks.

- subgenreid is a foreign key that references id in the genre table.
- genreid is also a foreign key that references id in the genre table.
- There is no subgenre table

This is probably a dodgy way to set these table up in the first place!

Dr Pepper

I've come up with a query that works. It a bit ugly but the result is the desired one:

select track.id, artist, title, g1.name, g2.name
from track, genre g1, genre g2
where track.genreid=g1.id and track.subgenreid=g2.id;

Anybody got any other (better) ways of doing this. The above query is quite slow.

futonic

do you have indexes on genreid and subgenreid?

amen

does every track have subgenre? I bet not.

so look up joins and and in particular left outer joins.

If this is MS SQL server then also look at locking hints

You could also run the query and use SQL Profiler to determine what the SQL is doing (I'm sure Oracle, MySQL have similar functions)