CODEWITHSUNDEEP
sqlpostgresql
MattDionis
MattDionis

Reputation: 3616

Replace NULL column after SQL UNION

I am using a UNION to combine the results of two select statements. In one statement I find the identity of a sender and in the other I find the identity of a recipient. The problem I'm running into is that my row count is always twice what it "should" be as there will always be a "blank" sender for the recipient results and a "blank" recipient for sender results. How can I restructure this to return one row for each transaction which has both sender and recipient filled in?

(
  SELECT s.id, s.sender_id, '' AS sender, NOW() AS sender_create_date, s.recipient_id,
    MAX(c.identity) AS recipient, MAX(c.create_date) AS recipient_create_date
  FROM db.sales s
  LEFT JOIN db.customers c ON c.participant_id = s.recipient_id
  WHERE sender_id = $1
  OR recipient_id = $1
  GROUP BY s.id
)
UNION
(
  SELECT s.id, MAX(c.identity) AS sender, MAX(c.create_date) AS sender_create_date, s.recipient_id,
    '' AS recipient, NOW() AS recipient_create_date
  FROM db.sales s
  LEFT JOIN db.customers c ON c.participant_id = s.recipient_id
  WHERE sender_id = $1
  OR recipient_id = $1
  GROUP BY s.id
)

Here is an example of my current results for one transaction:

id | sender | sender_create_date | recipient | recipient_create_date
1  |  bob   |     11/1/2016      |           |    11/22/2016
1  |        |     11/22/2016     |    jill   |    11/5/2016

What I am aiming for is:

id | sender | sender_create_date | recipient | recipient_create_date
1  |  bob   |     11/1/2016      |   jill    |    11/5/2016

Upvotes: 2

Views: 1622

Answers (1)

MattDionis
MattDionis

Reputation: 3616

Turns out what I was aiming for can be accomplished with two joins:

SELECT s.id, MAX(cs.identity) AS sender, MAX(cs.create_date) AS sender_create_date, MAX(cr.identity) AS recipient, MAX(cr.create_date) AS recipient_create_date
FROM db.sales s
LEFT JOIN db.customers cr ON cr.participant_id = s.recipient_id
LEFT JOIN db.customers cs ON cs.participant_id = s.sender_id
WHERE sender_id = $1
OR recipient_id = $1
GROUP BY s.id;

Upvotes: 1

Related Questions