SQL select multiple rows from foreign table for multiple columns

Question

In our PostgreSQL db, we have a table called 'tokens':

token_id | file_cc | file_cc2 | file_cc3 | file_ob

All the columns prefixed with 'file_' contain id's to the 'files' table:

file_id | name | location | hash

What I want is to select a token and to the resultset add the name and location from the files table for all the file columns.

With a JOIN this doesn't seem do-able, because you can only join on one column:

select * from tokens t
left join files f
on (t.file_cc = f.file_id)

Any ideas on this?

Answer 1

You either have to join to the files table 4 times or use a query like the following, which queries tokens 4 times to get the file_ids (this should be cheap due to indexes) and then runs one query over the files table.

select * from files 
where    file_id in (select file_cc  from tokens where token_id = ?)
      or file_id in (select file_cc2 from tokens where token_id = ?)
      or file_id in (select file_cc3 from tokens where token_id = ?)
      or file_id in (select file_ob  from tokens where token_id = ?);

Answer 2

I'm pretty sure you can have multiple conditions in the ON clause. So you should be able to do:

SELECT * FROM tokens t
LEFT JOIN files f
   ON (t.file_cc = f.file_id OR
       t.file_cc2 = f.file_id OR
       t.file_cc3 = f.file_id OR
       t.file_ob = f.file_id);

If that doesn't work, you could convert the ON conditions to WHERE conditions:

SELECT * FROM tokens t
LEFT JOIN files f
   WHERE 
       t.file_cc = f.file_id OR
       t.file_cc2 = f.file_id OR
       t.file_cc3 = f.file_id OR
       t.file_ob = f.file_id;

and it should achieve the same results.