StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POProblem with MySQL left join
text
Body
copied!<p>I have 3 tables in MySQL (5.1) : account, bank_account_data and investment_account_data. </p> <p>the tables are define as follows: (NOTE: this is a simplified version the actual tables have more columns and the bank and investment data has different information that's not needed for this particular case but needed elsewhere)</p> <pre><code>CREATE TABLE account ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, type ENUM ('INVESTMENT', 'BANK') NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; CREATE TABLE investment_account_data ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account_id BIGINT UNSIGNED NOT NULL, as_of_date TIMESTAMP NULL DEFAULT NULL, total_balance NUMERIC(12, 4) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (account_id) REFERENCES account (id), CONSTRAINT unique_per_account_and_date UNIQUE (account_id, as_of_date) ) ENGINE=InnoDB; CREATE TABLE bank_account_data ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account_id BIGINT UNSIGNED NOT NULL, as_of_date TIMESTAMP NULL DEFAULT NULL, current_balance NUMERIC(12, 4) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (account_id) REFERENCES account (id), CONSTRAINT unique_per_account_and_date UNIQUE (account_id, as_of_date) ) ENGINE=InnoDB; </code></pre> <p>Currently I have only one account in the account table with id = 1 and type = 'INVESTMENT' and I have one record in the investment_account_data table (id=1, account_id=1, total_balace=15000, as_of_data='2011-03-02 00:00:00')</p> <p>the bank_account_data has no rows.</p> <p>Running the following query (surprisingly) returns no rows:</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 bank_account_data BD ON (BD.account_id = A.id and A.type='BANK') LEFT JOIN investment_account_data ID ON (ID.account_id = A.id and A.type='INVESTMENT') WHERE A.id=1 </code></pre> <p>but this one returns one row (as expected) :</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 and A.type='INVESTMENT') LEFT JOIN bank_account_data BD ON (BD.account_id = A.id and A.type='BANK') WHERE A.id=1 </code></pre> <p>Any ideas to why I'm seeing these results ?</p> <p>Also if I remove the A.type condition from the join it will return one row as well. </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