CODEWITHSUNDEEP
sqlleft-join
Jake Ball
Jake Ball

Reputation: 808

SQL Statement with joins & where clause

I am still working on a Social Networking script, and I have pretty much finished it up now, however there is one problem I have been unable to resolve, that is outputting all of the posts by users AND the user logged in, the latter being the issue.

With the following statement, I have been able to get the status message to output, however other information such as their name and profile information is not.. joined? I think that is what is not happening:

So, when it is not the logged in user, their alongside their status displays, however when its the person logged in (e.g UserID 1, and yes.. I have removed the variable and just put a number) it just shows their message and doesn't access the user information.

SELECT * FROM al_posts
        LEFT JOIN al_friendships ON al_posts.user_id = al_friendships.user_b 
        LEFT JOIN al_users ON al_friendships.user_b = al_users.id
        WHERE al_friendships.user_a = '{$_SESSION['UserID']}' OR al_posts.user_id = '{$_SESSION['UserID']}'

The database structure for the tables in question: (note, the friendship table is not an issue so I have left it out)

al_posts table

al_posts table

al_users table

al_users table

If there is anything anyone notices wrong with the syntax, I would really appreciate it if you could explain what I did wrong so I can correct it. Thanks

Upvotes: 1

Views: 92

Answers (2)

Jake Ball
Jake Ball

Reputation: 808

I resolved this issue and forgot to mark it as resolved, essentially I got the joins in a real tangle. I sat back, thought about exactly what I wanted to achieve and this was the final working query that I came up with.

SELECT * 
    FROM al_posts
    JOIN al_friendships 
    ON al_posts.user_id = al_friendships.user_b 
    AND al_friendships.user_a = '{$_SESSION['UserID']}'                                     
    JOIN al_users ON al_friendships.user_b = al_users.id

Thanks for the help guys!

Upvotes: 0

user330315
user330315

Reputation:

Putting the condition on an outer joined table into the where clause turns it effictively into an inner join. You need to apply this in the join condition:

SELECT * 
FROM al_posts
  LEFT JOIN al_friendships 
         ON al_posts.user_id = al_friendships.user_b 
        AND al_friendships.user_a = '{$_SESSION['UserID']}'
  LEFT JOIN al_users ON al_friendships.user_b = al_users.id
WHERE al_posts.user_id = '{$_SESSION['UserID']}'

The reason why the condition will make this an inner join is that the rows that "do not" match come back with NULL values in the columns of the outer joined table. Comparing a value against NULL returns "undefined" and thus the condition is false, filtering out all rows that were "outer joined".

Upvotes: 2

Related Questions