Conditional JOIN based on column value

Question

I've looked all over, and unfortunately, I can't seem to figure out what I'm doing wrong. I'm developing a personal financial management application that uses a MySQL server. For this problem, I have 4 tables I'm working with.

The TRANSACTIONS table contains columns CATID and BILLID which refer to primary keys in the SECONDARYCATEGORIES and BILLS tables. Both the TRANSACTIONS and BILLS tables have a column PCATID which refers to a primary key in the PRIMARYCATEGORIES table.

I'm building a SQL query that sums an "amount" column in the TRANSACTIONS table and returns the primary key from PCATID and the sum from all records that are associated with that value. If the BILLID is set to -1, it should find the PCATID in SECONDARYCATEGORIES where SECONDARYCATEGORIES.ID = TRANSACTIONS.CATID, otherwise (since -1 indicates this is NOT a bill), it should find the PCATID from the BILL record where BILLS.ID matches TRANSACTIONS.BILLID.

I'm looking for something like this (not valid SQL, obviously):

SELECT
 SECONDARYCATEGORIES.PCATID,
 SUM(TRANSACTIONS.AMOUNT)
FROM
 TRANSACTIONS
IF (BILLID = -1) JOIN SECONDARYCATEGORIES ON SECONDARYCATEGORIES.ID = TRANSACTIONS.CATID
ELSE JOIN SECONDARYCATEGORIES ON SECONDARYCATEGORIES.ID = BILLS.CATID WHERE BILLS.ID = TRANSACTIONS.BILLID

I have tried a myriad of different JOINs, IF statements, etc, and I just can't seem to make this work. I had thought of breaking this up into different SQL queries based on the value of BILLID, and summing the values, but I'd really like to do this all in one SQL query if possible.

I know I'm missing something obvious here; any help is very much appreciated.

Edit: I forgot to describe the BILLS table. It contains a primary category, ID, as well as some descriptive data.

two left outer joins should be able to solve your issue here. however your BILLS table is missing — RoMEoMusTDiE, Dec 20 '17 at 19:52

score 9 · Accepted Answer · edited Dec 22 '17 at 13:40

9

You can use OR in your JOIN, like this:

SELECT S.PCATID,
       SUM(T.AMOUNT)
FROM TRANSACTIONS T 
LEFT JOIN BILLS ON BILLS.ID = T.BILLID 
JOIN SECONDARYCATEGORIES S ON (S.ID = T.CATID AND T.BILLID = -1)
                           OR (S.ID = BILLS.CATID AND BILLS.ID = T.BILLID)

edited Dec 22 '17 at 13:40

cameron

65
1
1
5

answered Dec 20 '17 at 20:16

Aaron Dietz

10,137
1
14
26

This is exactly what I was looking for. Somehow I was under the impression the AND/OR wasn't allowed in the ON portion of a JOIN. Thanks a lot for the help. – cameron Dec 20 '17 at 20:25
What is the performance impact of such conditional Joins? Is there any alternative way of not doing all the decision making in JOIN conditions? – SwapSays Apr 30 '21 at 11:11

score 1 · Answer 2 · answered Dec 20 '17 at 20:25

1

You can also use COALESCE and CASE in your JOINs.

SELECT ID = COALESCE(s.PCATID,b.PCATID)
    ,Total = SUM(t.AMOUNT)
FROM TRANSACTIONS t
LEFT JOIN BILLS b ON b.BILLID = CASE WHEN t.BILLID <> -1 THEN t.BILLID END
LEFT JOIN SECONDARYCATEGORIES s ON s.CATID = CASE WHEN t.BILLID = -1 THEN t.CATID END
GROUP BY COALESCE(s.PCATID,b.BILLID)

answered Dec 20 '17 at 20:25

digital.aaron

5,435
2
24
43

1

That's not a conditional join. That's doing the joins unconditionally and then picking one value or the other. That's not efficient. – Quolonel Questions Feb 28 '20 at 14:33
@QuolonelQuestions actually, if you compare the execution plan generated by my query and the one generated by the accepted answer's query, you will find that they are nearly identical, save for a couple extra `Compute Scalar`s in my plan. My solution is no more inefficient than including an `OR` in a join clause. – digital.aaron Feb 28 '20 at 21:23

score 0 · Answer 3 · answered Dec 20 '17 at 19:58

I use UNION to pick either query. But the second query obviously won't work because it's missing BILLS table.

SELECT SECONDARYCATEGORIES.PCATID
    , SUM(TRANSACTIONS.AMOUNT)
FROM TRANSACTIONS
JOIN SECONDARYCATEGORIES ON SECONDARYCATEGORIES.ID = TRANSACTIONS.CATID AND BILLID = -1
UNION
SELECT SECONDARYCATEGORIES.PCATID
    , SUM(TRANSACTIONS.AMOUNT)
FROM TRANSACTIONS
JOIN SECONDARYCATEGORIES ON SECONDARYCATEGORIES.ID = BILLS.CATID AND BILLID <> -1
WHERE BILLS.ID = TRANSACTIONS.BILLID

Conditional JOIN based on column value

3 Answers3