0

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.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

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.

Improve this question
2
  • I would test these queries by changing the type field of the test row to BANK. The assumption would be that the first query would then return a row and the second would return no rows. Commented Mar 4, 2011 at 1:36
  • Changing to type 'BANK' will indeed return one row in the first query and no rows in the second. Commented Mar 4, 2011 at 1:39

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I would think the condition is on the join (since it's all in the ON clause) so after the first left join I'd still have single row result set
1

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.

Improve this answer

3 Comments

this query works, but why do you think there is a problem with the schema
Aleksi, this is a simplified version of our current database, the account table contains many more columns and so do the investment_account_data and the bank_account_data
I'm sure there's a lot of thought and history to the current schema. There's a lot of performance or usability benefits to be gained by sometimes not normalizing the database all the way. There's just that potential of error that comes with at least the simplified version of the schema that pops out. Maybe it has been dealt with triggers or some form of constraints that alerts if something unexpected is about to happen. Or it is just programmed into the client software which might lead to bugs if somebody forgets that the enum type determines which account_data should be used.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.