How to join tables with condition in postgresql?-CodePudding

I have a PostgreSQL query like below

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

As my requirement,There is a separate variable like x=5

I want to join new table when x is greater than 0 (x>0) like below

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 inner join hotel AS D on C.hotelid=D.id

Can anyone tell me how can I apply this logic?

CodePudding user response：

not sure to understand, where your 'x' come from ? is it a variable wich will impact your request (in plpsql), or just a filter based on a value in a field ?

case 1 :

--in plpsql or function environnement : 
-- suposed x exists as parameter or var and x=5;
[...]
DECLARE req text;
BEGIN 
  IF x>0 THEN 
    req= 'Select * 
        FROM rate as A
        /*FULL*/ JOIN plan AS B on A.id=B.rateid
        /*FULL*/ JOIN room AS C on B.roomid=C.id
        /*FULL*/ JOIN hotel AS D on C.hotelid=D.id
    ';
   ELSE

    req= 'Select * 
        FROM rate as A
        /*FULL*/ JOIN plan AS B on A.id=B.rateid
        /*FULL*/ JOIN room AS C on B.roomid=C.id
    ';
  END IF;
  RAISE NOTICE 'req : %', req;
  execute req;
[...]

case 2 :

Select * 
FROM rate as A
/*FULL*/ JOIN plan AS B on A.id=B.rateid
/*FULL*/ JOIN room AS C on B.roomid=C.id
/*FULL*/ JOIN hotel AS D on C.hotelid=D.id  
 WHERE D.x > 5

case 2 other way :

Select * 
FROM rate as A
/*FULL*/ JOIN plan AS B on A.id=B.rateid
/*FULL*/ JOIN room AS C on B.roomid=C.id
/*FULL*/ JOIN hotel AS D on C.hotelid=D.id  AND D.x > 5

to know the type of JOIN you want : https://www.postgresql.org/docs/current/queries-table-expressions.html#id-1.5.6.6.5.6.4.3.1.1