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 | notan@email.com |
| 6 | afake@email.com |
|---------------------------------------|
Thanks so much in advance.
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.
You can get the clients with status = 1 and their users with only one query, by joining all three tables:
select clients.client_id, clients.client_name, users.user_id, users.emailaddress
from clients
inner join client_user_list on client_user_list.client_id = clients.client_id
inner join users on client_user_list.user_id = users.user_id
where clients.status = 1
order by clients.client_id, users.user_id
The following command should resolve this issue I hope it is userful.
select distinct use.emailaddress
from clients cli
inner join client_user_list cul on (cli.client_id=cul.client_id)
inner join users use on (cul.user_id = use.user_id)
where cli.status = 1