Help with grasping (INNER?) JOIN

Question

I'm having trouble building a query. I can do what I want in 3 different queries.

SELECT id FROM table1 WHERE url LIKE '%/$downloadfile'
put that in $url_id
SELECT item_id FROM table2 WHERE rel_id = '$url_id'"
put that in $item_id
SELECT rel_id FROM table2 WHERE rel_id = '$item_id' AND field_id = '42'"
put that in $user_id

But from reading examples on joins and inner joins I think there's a more elegant way. I cant wrap my brain around writing a better query (but would like to) I can describe how it should go:

table1 fields: id, url

table2 fields item_id, rel_id, field_id

I know the last part of table1.url (LIKE '%/$filename') with that I select table1.id. table1.id is equal to one entry in table2.rel_id. So get that and select the table2.item_id. In table2 there is another entry which has the same table2.item_id and it will have a table2.field_id = '42' And finally the value I need is the table2.rel_id where the table2.field_id was 42. I will fetch that value and put it in $user_id

Can this be done with one query using joins/inner joins?

Answer 1

SELECT url, second.rel_id AS user_id
FROM table1
INNER JOIN table2 AS first
  ON table1.id=first.rel_id
INNER JOIN table2 AS second
  ON first.item_id=second.rel_id
WHERE second.field_id='42'
  AND table1.url LIKE '%/:downloadfile'

Answer 2

Sure, should be something like:

SELECT t2_second.rel_id
FROM table1 t1
INNER JOIN table2 t2_first ON t1.id = t2_first.rel_id
INNER JOIN table2 t2_second ON t2_second.rel_id = t1_first.item_it
WHERE 
   t1.url = '%/:filename' AND
   t2_second.field_id = 42

You could even throw in a distinct so that each t2_second.rel_id is only returned once:

SELECT DISTINCT [...]

You might not need this, however, if t2_second.field_id = 42 will restrict the query to only return one row for each t2_second.rel_id

Answer 3

This the query that works for me, made one small change to Ignacio's answer:

SELECT url, second.rel_id AS user_id
FROM table1
INNER JOIN table2 AS first
  ON table1.id=first.rel_id
INNER JOIN table2 AS second
  ON first.item_id=second.item_id
WHERE second.field_id='42'
  AND table1.url LIKE '%/:downloadfile'