CODEWITHSUNDEEP
sqljoin
Halfpint
Halfpint

Reputation: 4077

SQL: Get posts made by friends

I want to write a query to retrieve all posts written by friends I have on an app.

My table structure is:

Entity:
---------------------
EntityID  -  PK
FirstName -  VARCHAR
LastName  -  VARCHAR

Friends:
---------------------
FriendID  - PK
Friend1   - INT
Friend2   - INT

Post:
---------------------
PostID    - PK
EntityId  - INT
Message   - VARCHAR

The above schema has been simplified to demo the structure.

So far I have tried the following, but it will only retrieve posts made by the user with the ID 5. How can I make the query return posts by this user and all of his/her friends?

SELECT 
   P.PostID,
   P.Message,
   E.FirstName,
   E.LastName,
FROM 
   Entity AS E
JOIN 
   Friends AS F
      ON (E.EntityId = F.Friend1 OR E.EntityId = F.Friend2)
INNER JOIN
   Posts AS P
      ON P.EntityId = F.Friend1 OR P.Entity_Id = F.Friend2
WHERE 
   E.EntityId = 5
ORDER BY
   P.PostID DESC

If I had the data set:

FriendId   |   Friend1   |   Friend2  |
    1             8             5
    2             9             5
    3             5             3
    4             2             4


PostId     |  EntityId  |  Message
    1             5           Hello
    2             8           Goodbye
    3             2           Morning

I would expect only hello and goodbye to be returned as the user id of 2 has no connection to 5, but all of 5s posts should be returned. I've been staring at this for awhile and cant seem to fathom it.

Upvotes: 1

Views: 510

Answers (2)

abl
abl

Reputation: 5968

SELECT 
  p.postid
  ,p.message
  ,e.firstname
  ,e.lastname
FROM posts p
  INNER JOIN entity e
  ON e.entityid = p.entityid
WHERE e.entityid IN (
  SELECT friend2
  FROM friends 
  WHERE friend1 = 5

  UNION 

  SELECT friend1 
  FROM friends 
  WHERE friend2 = 5    
)

returns:

postid    message    firstname    lastname
2         Goodbye    John8        Doe8

The post "hello" is not returned since its author is not a friend of user 5, but user 5 himself.

SQL Fiddle

Upvotes: 1

Tim3880
Tim3880

Reputation: 2583

Basically , you need only a simple query to get posts matching your criteria:

select * from post
where entityid in (select friend1 from friends where friend2 = 5 )
or entityid in (select friend2 from friends where friend1 =5)
or entityid = 5;

Then add the stuffs you need:

select p.postid, e.lastname,e.firstname,e.entityid , p.message from post p
join entity e on e.entityid=p.entityid    
where p.entityid in (select friend1 from friends where friend2 = 5 )
or p.entityid in (select friend2 from friends where friend1 =5)
or p.entityid = 5;

And now change it to a join version:

select p.postid, e.lastname,e.firstname,e.entityid , p.message from post p
join (
    select friend1 from friends where friend2 =5 
    union select friend2 from friends where friend1 =5 
    union select 5 from dual) m
on p.entityid = m.friend1 
join entity e 
on p.entityid=e.entityid;

Upvotes: 3

Related Questions