[SOLVED] SQL JOIN Problem, please help.

[ukscotth]

Hi,

 

Im trying to query 2 tables at the same time but Im having no luck, Ive tried learning all about the JOIN command but still I cant seem to get to grips with it, if anyone could help it would be most appreciated. 

 

Here is the structure of the 2 tables :

 

Joovili_feeds ( id, action_from, action, action_to, action_date)

 

Joovili_buddies (buddy_id, buddy_username, buddy_buddy, status)

 

Basically Its for a social networking site and Im trying to create a modification that will show a recent buddies activity feed to the current user, the same kind of thing thats on facebook, so it shows the last ten joovili_feeds but only ones that are relative to your buddies. 

 

If the action is from somebody that is not in your buddy list then it must not be shown.

 

I hope that makes sense.

 

Many thanks.

 

Scott

 

 

[fenway]

What's buddy_buddy?

[ukscotth]

Hi Fenway,

 

Basically buddy_username and buddy_buddy are the names of the 2 friends so if mark was toms friend, mark would be in the buddy_username field and tom would be in the buddy_buddy field or vice versa.

 

[ukscotth]

Ok ive nearly managed to do it using this :

 

 $get_feeds = mysql_query("SELECT joovili_feeds.* 

FROM joovili_feeds 

LEFT JOIN joovili_buddies
  
ON joovili_feeds.action_from = joovili_buddies.buddy_username

OR joovili_feeds.action_to = joovili_buddies.buddy_username

WHERE joovili_buddies.buddy_buddy = '".$user_info['username']."'

ORDER BY id DESC LIMIT 50");

 

The only problem is that it only brings back results if your name is stored in buddy_buddy and the friends name is stored in buddy_username.

 

I need it to work the other way round aswell so it will still give a result if your friends name is stored in buddy_buddy and your name is stored in buddy_username.

 

Hope that makes sense, can anyone help ?

 

Thanks in advance.

 

Scott

[fenway]

Then you'll need to issue the "opposite" query too, and UNION them.

[ukscotth]

ahh ok, so do i simply do this :

 

 $get_feeds = mysql_query("

SELECT joovili_feeds.* 

FROM joovili_feeds 

LEFT JOIN joovili_buddies
  
ON joovili_feeds.action_from = joovili_buddies.buddy_username

OR joovili_feeds.action_to = joovili_buddies.buddy_username

WHERE joovili_buddies.buddy_buddy = '".$user_info['username']."'

UNION

SELECT joovili_feeds.* 

FROM joovili_feeds 

LEFT JOIN joovili_buddies
  
ON joovili_feeds.action_from = joovili_buddies.buddy_buddy

OR joovili_feeds.action_to = joovili_buddies.buddy_buddy

WHERE joovili_buddies.buddy_username = '".$user_info['username']."'

ORDER BY id DESC LIMIT 50");

 

Thanks.

 

Scott.