Brad Turner Brad Turner - 8 months ago 24
SQL Question

Selecting from another table based on relation tables value

I have three tables in MySQL:

1) bank_accounts

- accounts_id (PRIMARY)
- accounts_account_number (UNIQUE)

2) bank_accounts_customers

- accounts_customers_id (PRIMARY)
- accounts_customers_account_id (INDEX)
- accounts_customers_customer_id (INDEX)

3) bank_customers

- customers_id (PRIMARY)
- customers_customer_number (UNIQUE)
- customers_title
- customers_first_name
- customers_middle_name
- customers_last_name

I need to get the Account Number stored in the bank_accounts table and the Customer Number stored in the bank_customer table. The table bank_accounts_customers stores a link between the customers and the accounts that they have so multiple customers can share one account. All tables in the database are indexed and using Foreign Keys to link them.

Im unsure if INNER JOIN or JOIN would work and how to do this?

I have attached an image of the database (that is not 100% complete).

The query that I have tried is:

SELECT `bank_accounts`.`accounts_account_number`, `bank_customers`.`customers_customer_number`
FROM `bank_accounts`, `bank_customers`
INNER JOIN `bank_accounts_customers`
ON bank_accounts_customers`.`accounts_customers_account_id` = `bank_accounts`.`accounts_id`

SELECT ba.accounts_account_number, bc.customers_customer_number 
FROM bank_accounts AS ba

INNER JOIN bank_accounts_customers AS bac
 ON bac.accounts_customers_account_id = ba.accounts_id

INNER JOIN bank_customers AS bc
 ON bc.customers_id = bac.accounts_customers_customer_id 

Should get what you want.