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
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;