Problem with MySQL left join

I have 3 tables in MySQL (5.1) : account, bank_account_data and investment_account_data.

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)

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;

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')

the bank_account_data has no rows.

Running the following query (surprisingly) returns no rows:

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

but this one returns one row (as expected) :

SELECT A.id as account_id,
       COALESCE(BD.as_of_date, ID.as_of_date) as as_of_date,
       COALESCE(BD.c开发者_如何学编程urrent_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

Any ideas to why I'm seeing these results ?

Also if I remove the A.type condition from the join it will return one row as well.

This is exactly the output you should be expecting given the queries and data that you posted.

In your first query, the first join condition specifies A.type = 'BANK'. So, after the first LEFT JOIN, you have a result set of zero rows, which you then LEFT JOIN on again. If your "left" side is zero rows, a LEFT JOIN (or INNER JOIN) will always also return zero rows.

For the second query, the order is reversed. So, after the first join, you have a single row result set. You now have 1 row on the "left" side for your second join.

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.

1. Select all accounts matching type 'BANK'
2. Join them with the corresponding bank account information
3. Select all account matching type 'INVESTMENT' left from the last result set i.e. all accounts matching type 'BANK'
4. Join an empty set with the corresponding investment account information.

The fix

The dirty and bad way to do this is to create a different set of join conditions

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'))

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.

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.

The problem lies in the way the left join is processed and the A.type condition. UNION would be the more appropriate operator for what you are trying to do.

SELECT account_id, as_of_date, current_balance as balance
       FROM bank_account_data
UNION
SELECT account_id, as_of_date, total_balance as balanceas
       FROM investment_account_data

(Insert a join incase you need additional information from the account table)

Also you might want to reconsider your database layout in general and merge the three into one, if they really have the same attributes and differ only in type.