Activity Feed - How to write the MySQL command

Question

I am trying to write an activity feed using a database structure like in this link How to implement the activity stream in a social network But I am not sure how to write the MySQL command for retrieving the data. Specifically, in the activity table, I have the typeidentifying which kind of activity it is and the source_ididentifying the primary key of that type of activity. I do not know how to use the join syntax to get all the details from the source_id

        SELECT 
            activity.user_id, activity.type, activity.source_id, activity.time, users.username, act1.detail, act2.detail
        FROM activity JOIN users JOIN act1
        ON activity.user_id = users.uid
        AND activity.source_id = act1.sid
        WHERE 
            activity.userTo = :uid 

If I use the above command, I can only get the rows where act1 has the primary key of the source_id, while it may be of type 2(act2). Plus, I have not included act2 in the command. How shoud the command be written to get the details I want? Or I should do it in a complete different way? Thanks!

Answer 1

For joining act1 and act2 i used a LEFT JOIN because source_id may be of type1 or type2 , try this:

SELECT 
    activity.user_id, 
    activity.type, 
    activity.source_id, 
    activity.time, 
    users.username, 
    act1.detail, 
    act2.detail
FROM 
    activity 
JOIN users 
    ON activity.user_id = users.uid
LEFT JOIN act1
    ON activity.source_id = act1.sid
LEFT JOIN act2
    ON activity.source_id = act2.sid
WHERE 
    activity.userTo = :uid