Postgresql using LEFT JOIN and RIGHT JOIN at the same time

join;postgresqltemporary-tables

I have two temporary tables that I have created using tampa, tampb clauses in PostgreSQL. I want to be able to get result when data from either side is missing (company1 or company2) but not both.

I have tried to use FULL JOIN but it does not work.

This is my sample SQL

SELECT *
FROM   (
           WITH tempa AS (
               SELECT a.id,
                      a.name,
                      a.section,
                      a.photo,
                      a."company1",
                      b."company2"
               FROM   (
                          SELECT a.id,
                                 s.name,
                                 s.section,
                                 s.photo,
                                 m.salary_company AS "company1",
                                 a.id
                          FROM   salary a
                                 JOIN pension b
                                      ON  a.pension_id = b.id
                                 JOIN company m
                                      ON  (
                                              m.id = a.salary_company_id JOIN users s ON s.id = a.users_id WHERE a.id = 
                                              35
                                              AND m.salary_company_id = 35
                                              AND a.amount IS NOT NULL GROUP BY a.pension_id,
                                              s.section,
                                              a.id,
                                              m.salary_company,
                                              s.name,
                                              s.roll,
                                              s.photo
                                          ) AS a
                                 LEFT JOIN (
                                          SELECT a.id,
                                                 s.name,
                                                 s.section,
                                                 s.photo,
                                                 m.salary_company AS "company2",
                                                 a.id
                                          FROM   salary a
                                                 JOIN pension b
                                                      ON  a.pension_id = b.id
                                                 JOIN company m
                                                      ON  (
                                                              m.id = a.salary_company_id JOIN users s ON s.id = 
                                                              a.users_id WHERE a.id = 22
                                                              AND m.salary_company_id = 22
                                                              AND a.amount IS NOT NULL GROUP BY a.pension_id,
                                                              s.section,
                                                              a.id,
                                                              m.salary_company,
                                                              s.name,
                                                              s.roll,
                                                              s.photo
                                                          ) AS b
                                                      ON  a.id = b.id
                                      ),
                                 tempb AS (
                                     SELECT *,
                                            COALESCE("company1", 0) + COALESCE("company2", 0) AS total,
                                            ROUND(
                                                (
                                                    (COALESCE("company1", 0) + COALESCE("company2", 0))::NUMERIC / (2)::
                                                    NUMERIC
                                                ),
                                                2
                                            ) AS average
                                     FROM   tempa
                                 )

SELECT *,
       RANK() OVER(ORDER BY average DESC) AS RANK
FROM   tempb) f WHERE f.id = 481

When data is available in company2 but not in company1, this SQL returns results (which is perfect because of LEFT JOIN) but I want to return results when either data are available in company1 and not in company2 or data are available in company2 and not in company1.
I have tried FULL JOIN, FULL OUTER JOIN, FULL INNER JOIN all does not work.

I will appreciate any advice given.

Final Results I want to obtain should look as follows

 
 
Name  Company1 Company2  Sum  Rank 

John $2000     $3000   $5000   1   
Doe  $1000     $2000   $3000   2
 
Eli   $500     $1000   $1900   3

Best Answer

Assuming compatible row types for tempa and tempb and you want exactly one result row, this is one of many possible solutions:

WITH cte AS (
   (SELECT ... LIMIT 1)  -- tempa
   UNION ALL
   (SELECT ... LIMIT 1)  -- tempb
   )
SELECT *
FROM   cte
WHERE (SELECT count(*) = 1 FROM cte);

Or for any number of result rows:

WITH tempa AS (SELECT ...)
   , tempb AS (SELECT ...)
SELECT * FROM tempa WHERE NOT EXISTS (SELECT 1 FROM tempb)
UNION ALL
SELECT * FROM tempb;
Related Question