Filling in missing fields in a SQL query

Question

I am trying to combine data from two tables Budget and Actuals into a simple table I can use in a PivotTable in Google Looker Studio to compare actuals and budgeted. I manage to combine the tables and the financial data but I would also like to transfer other data from the budget table, like the account names to the new table also for the rows coming from the actuals. I'm quite new to SQL and I think I need to do it in two steps but I have not found out how.

The tables look like this:

tblActual:

Date	Account	Balance
2023-01-01	1234	100
2023-01-02	1235	-100

tblBudget:

Date	Account	Balance	AccountType	AccountGroup
2023-01-01	1234	150	Sales	Income
2023-01-02	1245	-150	Raw material	Expenses

Now I have managed to combine these two with the following query:

SELECT coalesce(a.date, b.date) as date, coalesce(a.Account, b.Account) as Account, a.Balance as Outcome, Account as Budgeted, b.AccountGroup, b.AccountType
FROM tblActual a
full outer join tblBudget b 
ON (b.date = a.date and b.Account = a.Account)
ORDER BY datum DESC

That seems to work and I get a table like this:

Date	Account	Outcome	Budgeted	AccountType	AccountGroup
2023-01-01	1234	100	null	null	null
2023-01-02	1245	-100	null	null	null
2023-01-01	1234	null	150	Sales	Income
2023-01-02	1245	null	-150	Raw material	Expenses

But how can I make sure that the AccountTypes and AccountGroups in the new table are filled in also for the rows fetched from the tblActuals? So that all rows have their proper AccountTypes and AccountGroups (as defined in the tblBudget). Like the following:

Date	Account	Outcome	Budgeted	AccountType	AccountGroup
2023-01-01	1234	100	null	Sale	Income
2023-01-02	1245	-100	null	Raw material	Expenses
2023-01-01	1234	null	150	Sales	Income
2023-01-02	1245	null	-150	Raw material	Expenses

I am writing the query in Google BigQuery. Any help appreciated!

Best regards, Fred

Answer 1

From the data I see in the question you just need an inner join like so:

SELECT
       a.date
     , a.Account
     , a.Balance as Outcome
     , b.Budgeted
     , b.AccountGroup
     , b.AccountType
FROM tblActual a
INNER JOIN tblBudget b ON b.date = a.date and b.Account = a.Account
ORDER BY
       a.date DESC
     , a.Account

BUT, if those "dates" are timestamps (i.e. contain time not equal to midnight) then the inner join may not find any matches.