Mysql join from multiple tables

Question

I have 3 tables

friends
posts
members

friends
========
id, to, from (user_id's), status

there are 3 status's -1 = denied, 0 = no response/new, 1 = accepted



posts
=======
p_id, user_id (same as member_id), text


members
========
member_id, f_name, l_name

If like to select the text from the post in 'posts' combine it with the users name from 'members' and only display posts where the user_id is in the 'friends' table.

I would like to know if it can be done, I've tried an IN () statement in my query which worked, but it creates a new problem with generating the csv inside the IN (). I'd perfer to do this through mysql, but if it can't be done I may use a global variable to store friend data (but then it will not be upto date or will have to be refreshed when a user gets a new friend).

when a new friend request is sent it records who sent it and who it was to and the status of the request -1 = denied, 0 = no response, 1 = accepted. — arennaker
– arennaker, Commented Aug 28, 2012 at 7:21
@arennaker The status part of friends is missing from the schema description above, could you add it? — Joachim Isaksson
– Joachim Isaksson, Commented Aug 28, 2012 at 7:26
@arennaker If A and B are friends, is there only one row with status 1, or is there one for each direction? (ie only A B 1 or both A B 1 and B A 1)? — Joachim Isaksson
– Joachim Isaksson, Commented Aug 28, 2012 at 7:29

Joachim Isaksson · Accepted Answer · 2012-08-28 07:38:43Z

1

As I understand it, you want to find the name and posts of all your friends, not any friend that's in the friend table at all...?

Your own user id being in $myId, this should do it (newest posts first);

EDIT: Added status check for friends

SELECT m.f_name, m.l_name, p.`text`
FROM members m
JOIN posts p 
  ON m.member_id = p.user_id
JOIN friends f 
  ON f.`to` = m.member_id OR f.`from` = m.member_id
WHERE (f.`from` = $myId OR f.`to`= $myId)
  AND f.`status` = 1 AND m.member_id <> $myId
ORDER BY p.p_id DESC

edited Aug 28, 2012 at 7:38

answered Aug 28, 2012 at 7:20

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

arennaker Over a year ago

this works, but it only selects if f.from = $myID not if f.to = $myID

arennaker Over a year ago

this works great is it just does not include the members own post in the results, is there a way to change that?

Joachim Isaksson Over a year ago

@arennaker You mean include your own posts too? Just remove the AND m.member_id <> $myId at the end.

JDGuide · Accepted Answer · 2012-08-28 07:22:57Z

0

Try this :

    SELECT p.text,m.f_name,m.l_name FROM posts p 
    LEFT OUTER JOIN members m ON p.user_id=m.member_id
    where p.user_id in(select id from friends);

OR

   SELECT p.text,m.f_name,m.l_name FROM posts p 
   LEFT OUTER JOIN members m ON p.user_id=m.member_id
   INNER JOIN friends f on p.user_id=f.id

answered Aug 28, 2012 at 7:22

JDGuide

6,50112 gold badges52 silver badges67 bronze badges

1 Comment

arennaker Over a year ago

the issue is that it will only select data from the friends column 'to' or 'from' not both if either contains the $member_id

Chris Trahey · Accepted Answer · 2012-08-28 07:23:20Z

0

If I understand correctly, you have a user_id and you want all the posts authored by "friends" of that user. This query starts at posts, joins that to friends (where the author is the "destination" of the friendship) (at which point the WHERE clause will filter out any non-friend posts), and then joins in members to fill out the author's name info.

SELECT 
  posts.p_id
  posts.text,
  CONCAT(members.f_name, " ", members.l_name)
FROM 
  posts
  JOIN friends ON posts.user_id = friends.to
  JOIN members ON posts.user_id = members.member_id
WHERE
  friends.from = ?
GROUP BY posts.p_id

answered Aug 28, 2012 at 7:23

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

Comments

John Woo · Accepted Answer · 2012-08-28 07:38:19Z

I added a subquery to get all your friends since I assumed that if you have these records

Friends
==================================
ID      TO       FROM     STATUS
==================================
1       1        2         1
2       3        1         1

and your member_id = 1, your friends are 2, 3. right?

SELECT  b.f_name,
        b.L_name,
        c.`text`
FROM
    (
        SELECT `to` friendID
        FROM    friends
        WHERE   `status` = 1 AND
                `from` = ?             -- change this to your ID
        UNION
        SELECT `from` friendID
        FROM    friends
        WHERE   `status` = 1 AND
                `to` = ?               -- change this to your ID
    ) a  INNER JOIN members b
            ON a.friendID = b.member_ID
        LEFT JOIN   posts c
            ON a.friendID = c.user_id

Collectives™ on Stack Overflow

Mysql join from multiple tables

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related