Multiple Joins Question

[therealwesfoster]

Here is my query:

 

SELECT tasks.*, clients.*, projects.*, users.*

FROM tasks

LEFT JOIN clients ON tasks.task_client = clients.client_id

LEFT JOIN projects ON tasks.task_project = projects.project_id

LEFT JOIN users ON tasks.task_user = users.user_id

 

That query works. It grabs all the information I'm needing. But Since then, I've removed the "client" column from my "tasks" table. So I'm trying to modify the query like so:

 

SELECT tasks.*, clients.*, projects.*, users.*

FROM tasks

LEFT JOIN clients ON projects.project_client = clients.client_id

LEFT JOIN projects ON tasks.task_project = projects.project_id

LEFT JOIN users ON tasks.task_user = users.user_id

 

Note the bold part. See, I'm trying to get the client information based on the ID which is in the projects table. And I'm getting the project row based on the tasks project id. It's confusing.

 

It's like this, a 3-level join:

 

Task Project ID > Project Client ID > Client Information

Task Project ID > Project Information

Task User ID > User Information

 

(note the bold. it's the join im referring to)

 

Please help.

 

Thanks

[leest]

I had a similar problem a little while ago, but rather than having a long and  complicated join i added a sub query to extract the clients details. 

[fenway]

Why do you need the left join? And why not switch the 1st and 2nd left joins?

[therealwesfoster]

Why do you need the left join? And why not switch the 1st and 2nd left joins?

 

I actually tried switching them, but to no success. Could you provide an example of 3+ level joining?

 

Wes

[fenway]

Maybe I'm confused about what you're trying to retrieve... with *, you're getting everything.

[therealwesfoster]

Here's another explanation.

 

Task Table

task_id

task_project

 

Projects Table

project_id

project_client

 

Client Table

client_id

client_email

 

Now see, I'm trying to get the CLIENT_EMAIL by JOINing it to the Projects, which is JOINed to the tasks table. So it's a 3 level JOIN.

TASK PROJECT > PROJECT_CLIENT > CLIENT_INFO

 

I hope I'm making sense. Thanks

[fenway]

Where does users fit in?

 

Why all the LEFT JOINs?  Is this intentional?

[therealwesfoster]

The users is another table.. it's irrelevant to my question.

 

And I'm just doing the left joins because thats all i knew how to do.

 

If you can just tell me how to join 3 levels deep then thatd be it

[fenway]

Well, you already have the code for a 4 level join... 3 is no different.  Should be ok...

 

You only want left joins if any of the join conditions might be false... in this case, it doesn't sound like it, so inner join is better.

 

And I'm asking all of these questions because you said:

See, I'm trying to get the client information based on the ID which is in the projects table. And I'm getting the project row based on the tasks project id. It's confusing.

I don't understand what this means.

 

Do you want to get this info for all entries (since I don't see a where clause)?

 

Could you give an example of what you get now vs. what you want?