CODEWITHSUNDEEP
mysqlsqljoinaggregates
cameron
cameron

Reputation: 65

Conditional JOIN based on column value

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.

Upvotes: 6

Views: 15740

Answers (3)

Aaron Dietz
Aaron Dietz

Reputation: 10277

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)

Upvotes: 9

digital.aaron
digital.aaron

Reputation: 5707

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)

Upvotes: 1

Eric
Eric

Reputation: 3257

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

Upvotes: 0

Related Questions