How to join tables using CASE WHEN in postgresql?

Question

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?

Please read: Bad Habits to Kick : Using old-style JOINs

Luuk
– Luuk

2022-09-16 06:53:30 +00:00
Commented Sep 16, 2022 at 6:53 — Luuk
– Luuk, Commented Sep 16, 2022 at 6:53
I want to know how to apply the condition

Nirodha Wickramarathna
– Nirodha Wickramarathna

2022-09-16 06:58:57 +00:00
Commented Sep 16, 2022 at 6:58 — Nirodha Wickramarathna
– Nirodha Wickramarathna, Commented Sep 16, 2022 at 6:58

Luuk · Accepted Answer · 2022-09-16 06:59:59Z

1

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

answered Sep 16, 2022 at 6:59

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to join tables using CASE WHEN in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related