CODEWITHSUNDEEP
phpmysqlsql
Tyler Biscoe
Tyler Biscoe

Reputation: 2422

Fairly complex SQL statement using inner join (I presume)

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      |     [email protected]     |
|      6      |     [email protected]     |
|---------------------------------------|

Thanks so much in advance.

Upvotes: 1

Views: 118

Answers (3)

Patrick Kostjens
Patrick Kostjens

Reputation: 5105

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.

Upvotes: 3

Anderson Silva
Anderson Silva

Reputation: 384

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

Upvotes: 2

Christian Specht
Christian Specht

Reputation: 36421

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

Upvotes: 3

Related Questions