CODEWITHSUNDEEP
phpmysql
Dylan Cross
Dylan Cross

Reputation: 5986

Combining two MYSQL queries to get posts by friends only

I have a query to get the users friends, and one to get all posts users have posted as statuses, now I need to combine them.

Friends:

SELECT CASE WHEN userID=$session THEN userID2 ELSE userID END AS friendID 
FROM friends 
WHERE userID=$session OR userID2=$session

then users posts:

SELECT * FROM posts 
WHERE toID=fromID AND state='0' 
ORDER BY id DESC LIMIT 10

so both work as they should work together. friendID from the first query should equal toID or fromID, it doesn't matter which since they both need to be the same.

Upvotes: 0

Views: 453

Answers (2)

Nico Schertler
Nico Schertler

Reputation: 32627

Your second condition for the posts is that they are from friends. So:

SELECT * FROM posts  
WHERE toID=fromID AND state='0' AND toID IN
    (SELECT CASE WHEN userID=$session THEN userID2 ELSE userID END AS friendID  
     FROM friends  
     WHERE userID=$session OR userID2=$session)
ORDER BY id DESC LIMIT 10

Upvotes: 2

DarkAjax
DarkAjax

Reputation: 16223

You should use JOIN:

SELECT 
    CASE 
        WHEN userID=$session THEN userID2 
        ELSE userID 
    END AS friendID, 
    posts.*
FROM friends 
JOIN posts on posts.toID = friends.friendID
WHERE (userID=$session OR userID2=$session)
AND toID=fromID 
AND state='0' 
ORDER BY id DESC LIMIT 10

Upvotes: 0

Related Questions