Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 888
cameron Avatar asked Dec 20 '17 19:12

cameron


People also ask

How do you join different tables based on conditions?

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.

Can a case statement be used in a join?

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.

Can we use where condition before join?

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.

Can you join coalesce?

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.


2 Answers

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)
like image 128
Aaron Dietz Avatar answered Sep 25 '22 12:09

Aaron Dietz


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) 
like image 24
digital.aaron Avatar answered Sep 24 '22 12:09

digital.aaron