What is the logic behind this?

[Braxton]

Logic? I have no idea when it comes to this, but I will explain as best I can and then if someone could point my into the right direction as to where I could read more about this, that would be super! I would search google or even the forums for this, but I have no idea of how I would even really word this or what keywords.

 

I have table?(friend_collective) with the columns id|receiver|sender|friend_flag the receiver and sender columns are simply user ids where as friend_flag is purely either Y or N. now then I have a second table(status_collective) with the columns id|uid|status_message|time_posted the uid being the user id, the rest are pretty obvious.

 

Now what I am basically trying to do is get all the status messages for the friends of a specific user. I know how I can achieve this in PHP, but for my own learning purposes and also cause I want to know if this is even possible to achieve with mysql, meaning less php.

 

Hope this makes sense. Not even sure if I worded this correctly.

[Christian F.]

SELECT `id` FROM `friend_collective` WHERE `sender` =  $UserID AND `friend_flag` = 'Y'

Something like that, you mean?

[xyph]

SELECT sc.status_message, u.name

FROM friend_collective fc

JOIN users u ON u.id = fc.receiver

LEFT JOIN status_collective sc ON sc.uid = fc.receiver

WHERE fc.sender = $currentUserID AND fc.friend_flag = 'Y'

 

Untested, but should give you an idea of what you want to do. I've used a left-join for the status in case a user didn't have a status.

[Braxton]

SELECT sc.status_message, u.name

FROM friend_collective fc

JOIN users u ON u.id = fc.receiver

LEFT JOIN status_collective sc ON sc.uid = fc.receiver

WHERE fc.sender = $currentUserID AND fc.friend_flag = 'Y'

 

Untested, but should give you an idea of what you want to do. I've used a left-join for the status in case a user didn't have a status.

 

Okay this has got me on the right track. Perfect actually. The only thing that I don't understnad is how I can add in a second Join or Left join to also return my status posts. What I have so far.

SELECT u.username, sc.status_message AS status, sc.time_posted AS time
FROM friend_collective fc
JOIN user_collective u ON u.id = fc.receiver
LEFT JOIN status_collective sc ON sc.uid = fc.receiver
WHERE fc.sender = 1 AND fc.friend_flag = 'Y'
ORDER BY sc.time_posted DESC
LIMIT 20;

This does return results of the friends of id 1 but not id 1's own status messages. if a friend doesn't have a status posted though, returns null. Upon trying to play around with adding a second own join I got some really nasty lookin errors in my client.

 

 

[xyph]

All you're missing is the user's own status? I'd personally do that in a second query, otherwise you'll lose a few optimizations that query takes advantage of. It's possible to do it in a single query, but I doubt it's more efficient.

[Braxton]

yes and of course the null value. user 1 is friends with user 3 but user 3 have no status messages. So it returns a null result.

[xyph]

If the user doesn't have a status, of course it's going to return null. What would you expect it to return?

 

If you want to skip users without a status message, change the LEFT JOIN to a JOIN. I even mentioned in my post that I used a left join in case a user doesn't have a status.

[Braxton]

I even mentioned in my post that I used a left join in case a user doesn't have a status.

Well that part i did misunderstand, reading back at it I'm still kinda confused how its worded, but that is irrelevant.  I get what you are saying now and I see how it works some what. I've been reading over the sticky about joins and I'm still kinda "lolwut?"

[xyph]

LEFT JOIN will still return rows if there's no matching row in the ON clause.

 

A standard JOIN will only return rows where a match can be found.

[Braxton]

Thanks mate. That helped much.  If I have the following line. Do I then have to JOIN the status_collective table again to get the posts of the user(id 1) maybe I was doing something wrong cause I would get each status message sometimes twice and even three times.

JOIN status_collective sc ON sc.uid = fc.receiver

[xyph]

If you must do it in a single query, use a UNION.

 

 

SELECT 'myself' as username, status_message status, time_posted time FROM status_collective WHERE uid = $userID

UNION

SELECT u.username, sc.status_message, sc.time_posted

FROM friend_collective fc

JOIN user_collective u ON u.id = fc.receiver

JOIN status_collective sc ON sc.uid = fc.receiver

WHERE fc.sender = $userID AND fc.friend_flag = 'Y'

ORDER BY sc.time_posted DESC

LIMIT 20;