StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'd venture a guess that the reason for the seen behavior is that you create a filter over the type field on the first join clause that masks all the accounts from the second join clause.</p> <ol> <li>Select all accounts matching type 'BANK'</li> <li>Join them with the corresponding bank account information </li> <li>Select all account matching type 'INVESTMENT' left from the last result set i.e. all accounts matching type 'BANK'</li> <li>Join an empty set with the corresponding investment account information.</li> </ol> <p>The fix</p> <p>The dirty and bad way to do this is to create a different set of join conditions</p> <pre><code>SELECT A.id as account_id, COALESCE(BD.as_of_date, ID.as_of_date) as as_of_date, COALESCE(BD.current_balance, ID.total_balance) as balance FROM account A LEFT JOIN investment_account_data ID ON (ID.account_id = A.id) LEFT JOIN bank_account_data BD ON (BD.account_id = A.id) WHERE A.id=1 AND ((ID.account_id is not null and A.TYPE='INVESTMENT') OR (BD.account_id is not null and A.TYPE = 'BANK')) </code></pre> <p>Having to use OR like that makes me think that there's something smelly about the database schema. You can do the same with an UNION which is much better suited here.</p> <p>It seems to me that your intention is that the account can be either a bank account or an investment account. The database schema does not enforce that at the moment which lead to the need for such a query detailed in this question. The purpose for the account table seems to be to provide a space for all the account ids. It could be replaced with a sequence. I'm also not sure you need to have different table for bank accounts and investment accounts based on the current definition. You could just do the same with two tables, one containing account information and the other the account balance per date. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload