complex'ish SQL joins across multiple tables with multiple conditions across all tables

Question

Given the following tables:

labels              tags_labels  
|id   |name   |     |url   |labelid |  
|-----|-------|     |/a/b  |1       |  
|1    |punk   |     |/a/c  |2       |  
|2    |ska    |     |/a/b  |3       |
|3    |stuff  |     |/a/z  |4       |  

artists             tags  
|id  |name    |     |url   |artistid   |albumid  |  
|----|--------|     |------|-----------|---------|  
|1   |Foobar  |     |/a/b  |1          |2637     |
|2   |Barfoo  |     |/a/z  |2          |23       |  
|3   |Spongebob|    |/a/c  |1          |32       |

I would like to get a list of urls that match a couple of conditions (which can be entered by the user into the script that uses these statemets). For example, the user might want to list all urls that have the labels "(1 OR 2) AND 3", but only if they are by the artists "Spongebob OR Whatever".

Is it possible to do this within a single statement using inner/harry potter/cross/self JOINs? Or would I have to spread the query across multiple statements and buffer the results inside my script?

Edit:
And if it is possible, what would the statement look like? :p

Answer 1

Yes, you can do this in one query. And maybe an efficient way would be to dynamically generate the SQL statement, based on the conditions the user entered.

Answer 2

This query would allow you to filter by label name or artist name. Building the sql dynamically to concatenate the user parameters or passing the desired parameters into a stored procedure would obviously change the where clauses but that really depends on how dynamic your 'script' must be...

SELECT tl.url
FROM labels l INNER JOIN tags_labels tl ON l.id = tl.labelid
WHERE l.name IN ('ska','stuff') 
UNION (
SELECT t.url
FROM artists a INNER JOIN tags t ON a.id = t.artistid
WHERE a.name LIKE '%foo%'
)

Good Luck!