Tuples in a where clause

Oumayma El Jahrani 0
Viewed 16

I have the following tables

Table 1

Project Currency Amount1
P1 USD 100
P1 EUR 1500
P2 EUR 300

Table 2

Project Currency Amount2
P1 USD 200
P2 EUR 350

From these tables above , i want to be able to select Amounts from Table 1 but only for couples (Project , Currency) that exist in table 2 . So the result would be :

Project Currency Amount2
P1 USD 100
P2 EUR 300

I already know how to achieve this using a left join :

SELECT T2.Project
    ,T2.Currency
    ,T1.Amount
FROM Table2 T2
LEFT JOIN TABLE1 T1 
    ON T1.Project = T2.Project
    AND T1.Currency = T2.Currency

But i wonder if it's possible to put a where clause condition on a tuple , and having a query like this :

SELECT * 
FROM T1 
WHERE (Project,Currency) IN (SELECT DISTINCT ( Project,Currency) From Table1 )

For now running this query gives me table 1 as a result .

Thank's in advance for your help !

where-clause tuples postgresql
1 Answers

Yes, it's possible. But your subquery is wrong as it only has a single column, not two. Those parentheses create a single column which is an anonymous record type. Additionally the DISTINCT is useless in a query used for an IN condition. Given your problem description you also want to select from table 2, not table1

SELECT * 
FROM table_1
WHERE (Project,Currency) IN (SELECT Project,Currency 
                             From Table_2)
a_horse_with_no_name0
Related