How to join tables using CASE WHEN in postgresql?

Nirodha Wickramarathna 0
Viewed 22

I have a PostgreSQL query like below and I want to join a new table to that when rateid>100

CASE WHEN rateid<100 Then
       Select * FROM    rate as A, plan AS B, room AS C
                        WHERE   A.id=B.rateid
                                AND B.roomid=C.id
   
     ELSE
       Select * FROM    rate as A, plan AS B, room AS C, hotel AS D
                        WHERE   A.id=B.rateid
                                AND B.roomid=C.id
                                AND C.hotelid=D.id
     END

Can I know is there any way to join hotel table when rateid>100?

case sql postgresql
1 Answers

  • In your question you say "when rateid>100", I assume you mean "rateid>=100" because in the code you use "rateid<100"

  • I also changed the old-style to new-style joins.

  • The on-clause tells SQL what to join, so adding "rateid>=100" should solve the problem (When I understand your question correctly)

select
    *
from
    rate as A,
inner join plan as B on
    A.id = B.rateid
inner join room as C on
    B.roomid = C.id
    and rateid >= 100
Luuk0
Related