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.
You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.
There are plenty of ways to resolve for this: a subquery with a CASE statement in the join statement for the table you are joining in, a CASE statement in a temp table where all values are changed to match, or this handy little trick of using a CASE statement in the JOIN's ON clause.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
A COALESCE function returns the first non-NULL expression from a specified list. Usually, we use COALESCE as one of the elements in the select list, however, it can be successfully used in the join conditions too.
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)
You can also use COALESCE
and CASE
in your JOIN
s.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With