CODEWITHSUNDEEP
mysql
R_D
R_D

Reputation: 13

MySQL Query in 3 Tables

I'm trying to get a MySQL query to give me related data 2 tables away. So one user would have access to 2 projects, with a total of 3 posts (in "stream"). Essentially, I want to get the associated projects in project_users and then get posts in stream associated those projects.

The data looks something like this:

Table 1: user (1 user identified by 'id')

Table 2: project_users (user associated with 2 projects in this case)

Table 3: stream (posts attached to different projects, but I should get 3 based on my test data set)

My (broken) query looks like this:

SELECT U.`id`, P.`kf_users`, P.`kf_projects`, S.`kf_projects`, S.`body`
    FROM users U
    LEFT JOIN projects_users P ON U.`id` = P.`kf_users`
    LEFT JOIN stream S ON P.`kf_users` = S.`kf_projects`
    WHERE U.`id` = 1

What on earth am I missing? I tried subqueries, but it wouldn't let me have multiple rows in it, rendering it useless in the form I had it.

Thanks!

Upvotes: 1

Views: 114

Answers (2)

R_D
R_D

Reputation: 13

So I figured out what I did wrong. I was joining from the users table before, which wasn't necessary since my PHP script already has the user ID.

Instead, I joined from the projects_users table and took out references to the users table:

SELECT S.*
    FROM projects_users P
    LEFT JOIN stream S ON P.`kf_projects` = S.`kf_projects`
    WHERE P.`kf_users` = '1';

...and it worked like a charm. Thanks all for your help!

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 839224

I get no results any time I try this query

Really?! Then try changing it to this:

SELECT *
FROM users U
WHERE id = 1

I suspect that will give you no results too. Knowing that this query also fails should hopefully help you to find the problem more quickly (you don't have a user with id 1).

Note: If this gives you a result, then the query you posted above with the LEFT JOIN should return some rows too, because adding a LEFT JOIN shouldn't remove any rows.

Update: Try adding aliases to make the column names unique:

SELECT
    U.id,
    P.kf_users,
    P.kf_projects AS p_kf_projects,
    S.kf_projects AS s_kf_projects,
    S.body
FROM ....

Upvotes: 2

Related Questions