CODEWITHSUNDEEP
mysqljoin
arennaker
arennaker

Reputation: 53

Mysql join from multiple tables

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).

Upvotes: 0

Views: 9524

Answers (4)

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

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

Upvotes: 1

John Woo
John Woo

Reputation: 263693

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

Upvotes: 0

Chris Trahey
Chris Trahey

Reputation: 18290

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

Upvotes: 0

JDGuide
JDGuide

Reputation: 6525

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

Upvotes: 0

Related Questions