CODEWITHSUNDEEP
sqlpostgresql
user8545255
user8545255

Reputation: 839

left join based on conditions in postgresql

When i executes below query ,it throws me error saying : syntax error at or near "as".

How do we resolve this ?

Is there any other approach?

(select * from my_table1 where status='6') as a
left JOIN 
(select * from my_table1 where status='1') as b
ON (a.application_id = b.application_id)

Upvotes: 0

Views: 135

Answers (4)

Ivan Cézanne
Ivan Cézanne

Reputation: 78

select * from my_table1 as A 
LEFT JOIN  my_table1 as B
ON (A.application_id = A.application_id)
WHERE A.status='6' AND B.status='1'

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

You are missing the initial select * from clause. Moreover, there is no need for subqueries; you just need to be careful with the condition placement.

select *  
from my_table1 a 
left join my_table1 b on b.status='1' and
                         b.application_id = a.application_id
where a.status='6'

Upvotes: 1

Shadiqur
Shadiqur

Reputation: 492

you have select all values from you subquery the join them:

select a.* from (select * from my_table1 where status='6') as a
left JOIN 
select b.* from (select * from my_table1 where status='1') as b
ON (a.application_id = b.application_id)

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below by using a selection from subquery

select a.* from (select * from my_table1 where status='6') as a
left JOIN 
(select * from my_table1 where status='1') as b
ON a.application_id = b.application_id

Upvotes: 1

Related Questions