I have 3 tables. the first contains customer_group_id and account_number, linking each group to an account. The second contains customer_id and customer_group_id, linking each customer to a customer group and the third contains customer description by customer ID.
I would like to get customers info for all the customer in the customer_group in the account. The syntax I am using is as follows:
SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cgd.name AS customer_group
FROM customer c
LEFT JOIN customer_group_description cgd
LEFT JOIN customer_group cg
ON (c.customer_group_id = cgd.customer_group_id)
WHERE cg.account_number = '1'
Please, any help will be greatly appreciated.
customer_group:
--------------------------------------
| customer_group_id | account_number |
--------------------------------------
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
--------------------------------------
customer_group_description
--------------------------------------
| customer_group_id | name |
--------------------------------------
| 1 | group1 |
| 2 | group2 |
| 3 | group3 |
--------------------------------------
customer
-----------------------------------------------
| firstname | lastname | customer_group_id |
-----------------------------------------------
| john | smith | 1 |
| black | smith | 1 |
| gold | smith | 2 |
| bob | dylan | 3 |
-----------------------------------------------
I hope this will help clarify my question better. The idea is to get all the user that are under the account number 1.
Thank you.
