I have two tables: Account account_id2 | account_name` 11111 ABC 55555 HEY 99999 XYZ Journal jid | date | dbt_id | description | total_value | cr_id | 1 ...

Listing all transactions of an account from a specific period of time

I have two tables:

Account

account_id2  | account_name`
   11111        ABC
   55555        HEY
   99999        XYZ

Journal

jid | date       |  dbt_id  | description | total_value |  cr_id  |
 1    13/02/2020    11111        XXXXX         5000        55555
 2    15/02/2020    99999        YYYYY         100         11111

I need to list all transactions from a certain period of time of a certain account. For example, for the date 13/02/2020 – 15/02/2020 for account 11111,

The query should produce a result like below:

jid |    date    |  cr_name   |   debit   | description |   db_name   |  credit  |
 1    13/02/2020      HEY          5000        XXXXX                 
 2    15/02/2020                               YYYYY          XYZ         100

Here are what I have tried

-- Listing all CREDIT transactions with the name of the Debit account
SELECT j.jid, j.date, a.account_name as db_name, FORMAT(j.total_value,0) AS credit, j.description
FROM journal AS j INNER JOIN account AS a
ON j.dbt_id = a.account_id2
WHERE j.cr_id = 11111; 

-- Listing all DEBIT transactions with the name of the Credit account
SELECT j.jid, j.date, a.account_name as cr_name, FORMAT(j.total_value,0) AS debit, j.description
FROM journal AS j INNER JOIN account AS a
ON j.cr_id = a.account_id2
WHERE j.dbt_id = 11111;


-- So in an effort to join them
SELECT 
   j.jid, j.date, a.account_name AS cr_name, FORMAT(j.total_value,0) AS debit, j.description, 
   b.account_name AS dbt_name, FORMAT(k.total_value, 0) AS credit, k.description
FROM journal AS j
  INNER JOIN journal AS k
        ON k.dbt_id = j.dbt_id
  INNER JOIN account AS a
        ON j.cr_id = a.account_id2
  INNER JOIN account AS b
        ON k.dbt_id = b.account_id2
WHERE j.dbt_id = 11111 OR k.dbt_id = 11111;

But it produces every single combinations of entries that satisfies the WHERE condition

Please do help. I have searched various posts and tutorials and yet have not found similar case.

Answer

I beleive you are looking for the below.

select j.jid, , j.date, 
      iif(j.dbt_id=11111,null,d.account_name)    as dr_name,
      iif(j.dbt_id=11111,null,total)             as debit, 
      description, 
      iif(j.crd_id=11111,null,c.account_name)    as cr_name,
      iif(j.crd_id=11111, null,total)            as credit
from journals j join accounts d on a.account_id2 = j.dbt_id
                join accounts c on c.account_id2 = j.crd_id
where j.dbt_id = 11111 or j.crd_id = 11111

Advertisement

Answer