JOIN Repeating transactions and Split Transactions

Page 1 of 1
6 posts
JOIN Repeating transactions and Split Transactions

Conrado
New MMEX User

Posts: 5
Joined: Thu Jun 26, 2014 10:47 am
Hi i'm trying to make a report to show all the repeating transactions, but i have a problem with the split transactions.
When I make a JOIN with the Category and Subcategory tables and the transaction have a split it shows -1

How can i make the JOIN? I couldn't see any links in the database tables.
This is the query that I'm using:
Code: Select all
Select Bd.bdid, bd.TransDate, ac.AccountName, py.PayeeName, bd.TransCode, ca.CategName, sc.SubCategName, bd.NumOccurrences, bd.TransAmount, bd.Notes
from BillsDeposits_v1 AS bd
INNER JOIN Payee_v1 AS py
ON bd.PAYEEID=py.PAYEEID
INNER JOIN AccountList_v1 AS ac
ON bd.AccountId=ac.AccountId
INNER JOIN Category_v1 AS ca
ON bd.CategId=ca.CategId
INNER JOIN SubCategory_v1 AS sc
ON bd.SubCategId=sc.SubCategId
Order By Bd.bdid Asc
query.png
(30.71 KiB) Downloaded 1041 times

table.png
(30.17 KiB) Downloaded 1041 times


Thanks Conrado
Re: JOIN Repeating transactions and Split Transactions

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Here I've merged split categories for checking table

viewtopic.php?f=16&t=5509

left join should help
Re: JOIN Repeating transactions and Split Transactions

Conrado
New MMEX User

Posts: 5
Joined: Thu Jun 26, 2014 10:47 am
Nikolay wrote:Here I've merged split categories for checking table

viewtopic.php?f=16&t=5509

left join should help
Thanks for the reply. Please tell me is my analisis is right: the colums that JOINS the tables checkingaccount_v1 with the splittransactions_v1 in your query is "left join splittransactions_v1 s on s.transid=c.transid"  (line 28).
The problem is that BillsDeposits_v1 table haven't the column transid or the splittransactions_v1 table haven't the BDID column, so my question is
How the CheckingAccount transactions are generated from the reapeting transactions when they have a split category?
Re: JOIN Repeating transactions and Split Transactions

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Code: Select all
select 
case (case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end) when -1 then ca.categname 
    else ca.categname ||':'||sc.subcategname end category_name
, case ifnull(bd.categid, -1) when -1 then bs.categid else bd.categid end as categ_id
, case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end as subcateg_id
, bd.* 
, ca.*, sc.*
from BillsDeposits_v1 bd
left join budgetsplittransactions_v1 bs on bd.BDID = bs.TRANSID
INNER JOIN Payee_v1 AS py 
    ON bd.PAYEEID=py.PAYEEID
INNER JOIN AccountList_v1 AS ac
    ON bd.AccountId=ac.AccountId
INNER JOIN Category_v1 AS ca
    ON categ_id=ca.CategId
LEFT JOIN SubCategory_v1 AS sc
    ON subcateg_id=sc.SubCategId
Re: JOIN Repeating transactions and Split Transactions

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Nikolay wrote:
Code: Select all
select 
case (case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end) when -1 then ca.categname 
    else ca.categname ||':'||sc.subcategname end category_name
, case ifnull(bd.categid, -1) when -1 then bs.categid else bd.categid end as categ_id
, case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end as subcateg_id
, bd.* 
, ca.*, sc.*
from BillsDeposits_v1 bd
left join budgetsplittransactions_v1 bs on bd.BDID = bs.TRANSID
INNER JOIN Payee_v1 AS py 
    ON bd.PAYEEID=py.PAYEEID
INNER JOIN AccountList_v1 AS ac
    ON bd.AccountId=ac.AccountId
INNER JOIN Category_v1 AS ca
    ON categ_id=ca.CategId
LEFT JOIN SubCategory_v1 AS sc
    ON subcateg_id=sc.SubCategId
For more details 
viewtopic.php?f=12&t=533&p=1074&hilit=alldata#p1074
Re: JOIN Repeating transactions and Split Transactions

Conrado
New MMEX User

Posts: 5
Joined: Thu Jun 26, 2014 10:47 am
Nikolay thanks for the help
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 2 guests

cron