Select query where 3 values in a different select

SQL++
1

Hi
i am trying to run a simple query which should go like this:
SELECT * FROM COLLECTION1
WHERE (F1 , F2 , F3 ) IN
(SELECT C1 , C2 , C3 FROM COLLECTION2)

can you please assist how can I write it correctly ?

2 
SELECT *
FROM collection1 c1
WHERE [c1.f1,c1.f2,c1.f3] IN (SELECT RAW [c2.f1,c2.f2,c2.f3] FROM collection2 c2)

or you can construct an object to match:

WHERE {"a":c1.f1,"b":c1.f2,"c":c1.f3} IN (SELECT RAW {"a":c2.f1,"b":c2.f2,"c":c2.f3} FROM collection2 c2)

HTH.

3

Check out Subquery handling and potential query rewrite

WITH inlist AS (SELECT RAW [c2.f1,c2.f2,c2.f3] FROM collection2 c2)
SELECT *
FROM collection1 c1
WHERE [c1.f1,c1.f2,c1.f3] IN inlist;
4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.