使用内连接的相当复杂的SQL语句（我推测）

Question

I'm having trouble figuring out how to write an SQL query to return results from the following table structure.

The first thing I do is get a list of clients that have a status equal to 1 by:

SELECT * FROM clients WHERE status=1

Then I need to get all user email addresses that belong to a client. My plan was to loop through the results of the query above and running multiple queries for each client. As you can see from the table 'client_user_list' a single user can belong to multiple clients.

I tried doing something like this:

SELECT emailaddress 
FROM users 
INNER JOIN client_user_list ON users.user_id = client_user_list.user_id 
WHERE users.client_id = 1

But it failed. As you can see I'm a total novice when it comes to this stuff. Any help would be appreciated, or feel free to point me to an appropriate resource to learn more. I've looked, but I haven't found anything that covers something complex like this.

Additional info: Using foreign keys there are relationships between clients <-> client_user_list and client_user_list <-> users

clients:
|---------------------------------------|
|  client_id  |  client_name   | status |
|---------------------------------------|
|      1      |    John Doe    |    1   |
|      2      |    James Doe   |    0   |
|---------------------------------------|

client_user_list:
|----------------------|
| client_id  | user_id |
|----------------------|
|     1      |    5    |
|     2      |    6    |
|     1      |    6    |
|----------------------|

users:
|---------------------------------------|
|   user_id   |       emailaddress      |
|---------------------------------------|
|      5      |     [e-mail]     |
|      6      |     [e-mail]     |
|---------------------------------------|

Thanks so much in advance.

Answer 1

I'm not sure if this is your only problem, since you didn't specify what the exact problem is, but the WHERE-clause of your query contains an error. You query should be changed into this:

SELECT DISTINCT emailaddress 
FROM users 
INNER JOIN client_user_list ON users.user_id = client_user_list.user_id 
WHERE client_user_list.client_id = 1

The users table does not have a field called client_id, the client_user_list table does.