Postgresql using LEFT JOIN and RIGHT JOIN at the same time

Question

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

• Erwin Brandstetter: I ignored the code dump (it is invalid). You might clean up and focus on the core of the question.

Answer 1

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;

• Ephra: Thanks Erwin Brandstetter, but using UNION ALL will create two rows which will not give me exactly output I need. May you suggest an alternative to give me one row after joining ?
• Erwin Brandstetter: @Ephra: Please clarify in the question how many rows can come from each subquery (0-n?) and how many rows you want in the result - and which to pick if we there can be more in a single subquery. Then notify me again.
• Ephra: Hello @Erwin, depending on the number of company(x) available in the table, I can join as many companies to do the comparison, so I usually generate this SQL in PHP codes to give me number of companies I need to join.