Append SQL columns based on different JOINs

Question

My problem is I have a table where I want to return a column from an SQL database as two different columns based on different JOIN conditions.

My two select statements:

My first statement returns the check numbers and amounts for all of our checks that have gone through the process of being deposited -> and then ipaced (just a term).

SELECT COURTESY_CHECK.CHECK_NUMBER,
COURTESY_CHECK.CHECK_AMOUNT, 
CHECK_DEPOSIT.ID AS DEPOSIT_ID
FROM COURTESY_CHECK
INNER JOIN CHECK_DEPOSIT ON CHECK_DEPOSIT.COURTESY_CHECK_ID = COURTESY_CHECK.ID
INNER JOIN DEPOSIT ON CHECK_DEPOSIT_ID = DEPOSIT.ID 
INNER JOIN IPAC ON DEPOSIT.ID = IPAC.DEPOSIT_ID

My second statement returns the check numbers and amounts of the checks that have just been deposited.

SELECT COURTESY_CHECK.CHECK_NUMBER, 
COURTESY_CHECK.CHECK_AMOUNT, 
CHECK_DEPOSIT.ID AS DEPOSIT_ID 
FROM COURTESY_CHECK
INNER JOIN CHECK_DEPOSIT ON CHECK_DEPOSIT.COURTESY_CHECK_ID = COURTESY_CHECK.ID

I would like to have a table like

IPAC/DEPOSITED AMOUNT   DEPOSITED AMOUNT  CHECK_NUMBER
---------------------   ----------------  ------------
$4.00                                      123456
                        $5.00              654321

I'm using BIRT to compile reports and it really only allows you to chart data based on single data sets (which is a single query) AFAIK. I'd like to chart the total "IPAC/Deposited" amount versus the "Deposited" amount.

INNER JOIN IPAC ON DEPOSIT.ID = IPAC.DEPOSIT_ID should've been INNER JOIN IPAC ON CHECK_DEPOSIT.IPAC_ID = IPAC.ID — Leo
– Leo, Commented Oct 8, 2014 at 15:51

Rocky · Accepted Answer · 2014-10-07 21:37:37Z

1

You're really close. Wouldn't a UNION query do the trick? Something like this:

SELECT COURTESY_CHECK.CHECK_NUMBER,
COURTESY_CHECK.CHECK_AMOUNT AS [IPAC AMOUNT], 
null AS [DEPOSITED AMOUNT],
CHECK_DEPOSIT.ID AS DEPOSIT_ID
FROM COURTESY_CHECK
INNER JOIN CHECK_DEPOSIT ON CHECK_DEPOSIT.COURTESY_CHECK_ID = COURTESY_CHECK.ID
INNER JOIN DEPOSIT ON CHECK_DEPOSIT_ID = DEPOSIT.ID 
INNER JOIN IPAC ON DEPOSIT.ID = IPAC.DEPOSIT_ID 

UNION

SELECT COURTESY_CHECK.CHECK_NUMBER, null AS [IPAC AMOUNT],
COURTESY_CHECK.CHECK_AMOUNT AS [DEPOSITED AMOUNT], 
CHECK_DEPOSIT.ID AS DEPOSIT_ID 
FROM COURTESY_CHECK
INNER JOIN CHECK_DEPOSIT ON CHECK_DEPOSIT.COURTESY_CHECK_ID = COURTESY_CHECK.ID

Edit ... this CASE statement with outer joins might be more efficient -- I think this would work, too:

SELECT COURTESY_CHECK.CHECK_NUMBER,
CASE WHEN IPAC.DEPOSIT_ID IS NOT NULL THEN COURTESY_CHECK.CHECK_AMOUNT 
ELSE NULL END AS [IPAC AMOUNT],
CASE WHEN IPAC.DEPOSIT_ID IS NOT NULL THEN NULL ELSE COURTESY_CHECK.CHECK_AMOUNT
END AS [DEPOSITED AMOUNT],
CHECK_DEPOSIT.ID AS DEPOSIT_ID
FROM COURTESY_CHECK
INNER JOIN CHECK_DEPOSIT ON CHECK_DEPOSIT.COURTESY_CHECK_ID = COURTESY_CHECK.ID
LEFT JOIN DEPOSIT ON CHECK_DEPOSIT_ID = DEPOSIT.ID 
LEFT JOIN IPAC ON DEPOSIT.ID = IPAC.DEPOSIT_ID

edited Oct 7, 2014 at 21:37

answered Oct 7, 2014 at 21:30

Rocky

4956 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rocky Over a year ago

actually, a CASE statement will be more efficient . . . hold on, working on it...

Leo Over a year ago

Thanks so much for the help! I knew about the UNION operator, but it was concatenating the columns not appending them. switching out null for columns appends them?

Leo Over a year ago

I didn't try your CASE example yet, but am willing to learn more about it. Thanks for the alternative.

Collectives™ on Stack Overflow

Append SQL columns based on different JOINs

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related