0

I want to implement the below query

Select * from table1
where a in(select a,b,c,d,e from table2 order by date desc limit 5)

But IN statement is allowing only 1 column.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Gulya
  • 101
  • 9

2 Answers2

1

The fact that you need to do this suggests your design could use a rethink. But assuming the goal is to compare "a" from table1 separately to each column's value for table2, you can do it like this:

 ... in (select unnest(array[a,b,c,d,e]) from (select * from table2 order by date limit 5) foo )
jjanes
  • 37,812
  • 5
  • 27
  • 34
  • The following error is occurring; ERROR: `subquery in FROM must have an alias LINE 1: ..." where a in(select unnest(array[a,b,c,d,e]) from (select * ... ^ HINT: For example, FROM (SELECT ...) [AS] foo. SQL state: 42601 Character: 79` – Gulya Sep 17 '22 at 14:51
  • Fixed. The dummy alias isn't needed in the current dev version ( the future v16) which I tested with. – jjanes Sep 17 '22 at 17:17
0

You can use the where clause in following

Select * from table1
where (a,a,a,a,a) in(select a,b,c,d,e from table2 order by date desc limit 5)
Sund'er
  • 666
  • 1
  • 4
  • 11
  • Based on the above query, the outcome is 0. As @jjanes mentioned, basically I want to compare "a" from table1 separately to each column's value for table2 – Gulya Sep 17 '22 at 15:02