CODEWITHSUNDEEP
mysql
Samuel Mwamburi
Samuel Mwamburi

Reputation: 145

MySQL, Filter records in a table based on values in two other separate tables

I am trying to filter financial records on a MySQL table A based on mapping constraints stored on two separate tables B and C where: i. Table 1 has Budget and Expenditure amounts by Project Donors, Work Breakdown Structure IDs , and Year

wbs year    donor   budget  exp
1   2019    a         10    10
2   2019    b         11    11
3   2020    c         12    12
4   2020    d         13    13

ii. Table 2 has Project IDs and their constituent Work Breakdown Structure IDs

project wbs
001     1
002     2
003     3
001     4
002     5
003     6

iii. Table 3 has Project IDs and corresponding Donors.

project donor
001     a
002     b
003     c
001     d
002     a
003     b

I would like to view the total budgets and expenditures for all projects listed. I have written the following query:

SELECT
    `t2`.`project` AS `project`,
    `t2`.`wbs` AS `wbs`,
    `t1`.`year` AS `year`,
    `t1`.`grant` AS `grant`,
    `t1`.`funded_program_key` AS `funded_program_key`,
    `t1`.`budget` AS `budget`,
    `t1`.`exp` AS `exp`
FROM
    `wbs_table` `t2` 
    
    LEFT JOIN `expenditure_table` `t1` ON  `t2`.`wbs` = `t1`.`wbs`
    
    LEFT JOIN `donor_table` `t3` ON  `t1`.`donor` = `t3`.`donor`
    
WHERE `t3`.`project` = `t2`.`project` 

AND `t3`.`project` IS NOT NULL

However, figures appear to be missing when it is run and I can't seem to figure out where I'm going wrong.

This is what my desired table result should look like:

project   wbs   year  donor  budget  exp
001       1     2019    a    10      10
002       2     2019    b    11      11
003       3     2020    c    12      12

Upvotes: 0

Views: 39

Answers (1)

Akina
Akina

Reputation: 42612

The logic of process is not described at all.

But, looking on column names and values I propose the next query:

SELECT project, wbs, e.year, donor, e.budget, e.exp
FROM expenditure_table e
JOIN wbs_table w USING (wbs)
JOIN donor_table в USING (project, donor);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0ed23d0bcee9c4dc3f12eea383c4aa79

Upvotes: 1

Related Questions