Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Two columns in subquery in where clause

Tags:

sql

postgresql

I have a query:

SELECT s.period, s.year, s.amount 
FROM salaries s

I would like to select from salaries table only the rows that have period and year equal to:

SELECT p.period, p.years 
FROM periods p

I know that the simplest way would be use join, but the problem is - that in the application I can add only a clause after WHERE

So the solution should be:

SELECT s.period, s.year, s.amount 
FROM salaries s 
WHERE ...

Is that possible?

EDIT: The result should be the same as:

SELECT s.period, s.year, s.amount 
FROM salaries s
  JOIN periods p ON s.period = p.period AND s.year = p.year
like image 208
robson Avatar asked Mar 25 '15 11:03

robson


1 Answers

You can use more than one column for an IN condition:

SELECT s.period, s.year, s.amount 
FROM salaries s
where (s.year, s.period) in (select year, period from periods)

But Gordon's not exists solution is probably faster.

like image 66
a_horse_with_no_name Avatar answered Nov 10 '22 00:11

a_horse_with_no_name