CODEWITHSUNDEEP
sqlpostgresql
Kiang Yit Lim
Kiang Yit Lim

Reputation: 23

inner join a temporary table

WITH  temp(k)  AS (VALUES (15252332445), (15253665080), (15241848778),
(11813086136), (15253665080) ) LEFT join hello.transaction_job B on
B.id=temp.k SELECT * FROM temp,B.id,B.msisdn

Trying to join a common table expression with standard table, but failed

Can you guys tell me why?

error im getting when i run the query

Upvotes: 2

Views: 406

Answers (3)

Ven
Ven

Reputation: 2014

You can also write using union within cte

WITH TEMP (k)
AS (
    SELECT 15252332445

    UNION ALL

    SELECT 15253665080

    UNION ALL

    SELECT 15241848778

    UNION ALL

    SELECT 11813086136

    UNION ALL

    SELECT 15253665080
    )
SELECT B.id, B.msisdn
FROM TEMP t
LEFT JOIN hello.transaction_job B ON B.id = t.k

Upvotes: 0

user330315
user330315

Reputation:

The SELECT needs to be before the JOIN as with every join 

WITH  temp(k)  AS (
   VALUES (15252332445), (15253665080), (15241848778),
          (11813086136), (15253665080)
)
SELECT B.id, B.msisdn
FROM temp t
  LEFT join hello.transaction_job B on  B.id = t.k;

Upvotes: 4

Vao Tsun
Vao Tsun

Reputation: 51529

maybe you wanted smth like:

WITH  temp(k)  AS (VALUES (15252332445), (15253665080), (15241848778),
(11813086136), (15253665080) ) 
, b as (select * from temp LEFT join hello.transaction_job B on
B.id=temp.k)
SELECT * FROM temp,B.id,B.msisdn

Upvotes: 0

Related Questions