1

Suppose that I have two tables:

tableA (id, name);
tableB (id, name);

I also have a textual file with a list of couples of ids:

id1a, id2a
id1b, id2b
...

I would like to run the following query:

select *
from (
id1a, id2a
id1b, id2b
...)
join tableA as a on a.id = id1
join tableB as b on b.id = id2;

How can I write such a query?

2

You can prepare a query with a VALUES expression:

SELECT a.*, b.*
FROM  (
   VALUES 
      (id1a, id2a)
     ,(id1b, id2b)
     , ...
   )   t(id1, id2)
JOIN   tablea a ON a.id = t.id1
JOIN   tableb b ON b.id = t.id2;

Or you COPY the values from the text file to a temporary table and proceed from there:

CREATE TEMP TABLE t(id1 int, id2 int);

COPY t FROM '/path/to/file.csv';

SELECT a.*, b.*
FROM   t
JOIN   tablea a ON a.id = t.id1
JOIN   tableb b ON b.id = t.id2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.