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). https://s32.postimg.org/ia56fgjth/Screen_Shot_2016_07_31_at_5_51_38_pm.png
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`