I have two tables:
1.Activity-- which stores all the activities from a user
2.User-- which stores all the registered users
I want to fetch all the posts and the likes to all posts in one query. A row is a post or like is decided by the value of type column.
Table Name - Activity
| actId | parentId | type | postedby |
---------------------------------------------------
| 100 | NULL | post | 100 |
| 200 | 100 | like | 200 |
| 300 | 100 | like | 300 |
| 400 | 100 | like | 400 |
| 500 | NULL | post | 100 |
| 600 | 500 | like | 600 |
Table Name - User
| userId | name |
-------------------
| 100 | Amit |
| 200 | Alok |
| 300 | Arjun |
| 400 | Arpeet |
| 600 | Amaan |
The output should be
| actId | name | LikedBy |
------------------------------------------
| 100 | Amit | Alok, Arjun, Arpeet|
| 500 | Amit | Amaan |
NOTE - I don't want this to be achieved using FOR XML PATH since it is not supported in SQLite which I am working on.
My query attempt was
SELECT a.id, u.name,
(select group_concat(u.name) from activity a, user u where a.id = u.id and a.type = 'like'
group by a.parent) as likedby
FROM activity a, user u
WHERE a.id = u.id and a.type = 'post'
The result I got was
| Id | name | LikedBy |
---------------------------------------
| 100 | Amit | Alok, Arjun, Arpeet|
You can see that the other row was missing from result.
Try this:
select t.id4id Id, n1.Name Name, group_concat(n2.name) Groups
from (select t1.actid id4id, t1.postedby id4name, t2.postedby id4groups
from Activity t1 join Activity t2 on t1.actid = t2.parentid) t join "user" n1 on t.id4name = n1.userid join "User" n2 on t.id4groups = n2.userid
group by id4id
Notes: 1. I assume that a post will always have a null value in parent