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