Joining multiple tables with NULL values to avoid duplication

Question

I am building a news feed from multiple tables status, events and tracks. The data retrieved from these tables should correspond to the user-id of all the users that I follow. On the face of it I thought this seemed simple enough and I could do this with a few joins.

Every row in each of the status, events and tracks table has unique ID and they are also unique from each other, this should make matters easier later. I have done this using a unique_id table with a primary key to retrieve ID's before inserting.

My trouble is upon joining everything together the values duplicate.

Example

If I have this data. ----------

**Status**

user-id = 1

id = 1

status = Hello Universe!


----------


**Events**

user-id = 1

id = 2

event-name = The Big Bang


----------


**Tracks**

user-id = 1

id = 3

track-name = Boom


----------

Assuming I follow user 1 I would want to retrieve this.

user-id ---- id ---- status ---- event-name ---- track-name

1            1       Hello       NULL            NULL
                     Universe

1            2       NULL        The Big Bang    NULL

1            3       NULL        NULL            Boom

But in reality what I would get is something like this.

user-id ---- status.id ---- events.id ---- tracks.id ---- status ---- event-name ---- track-name

1            1              2               3             Hello       The Big Bang     Boom
                                                          Universe

And that row would be repeated 3 times.

Most of the queries I have tried will get something along those lines.

SELECT * FROM users
INNER JOIN followers ON users.id = followers.`user-id`
LEFT JOIN status ON followers.`follows-id` = status.`user-id`
LEFT JOIN events ON followers.`follows-id` = events.`user-id`
LEFT JOIN tracks ON followers.`follows-id` = tracks.`user-id`
WHERE users.`id` = 2

I am using laravel, so eventually this query will be put into Eloquent format. If there is a simpler and a not performance degrading way of doing this in Eloquent please let me know.

Edit

I cannot use a UNION as there is a different number of values in each table. The example is simplified for ease of reading.

@frazz I have tried using a UNION but because I have different number of columns in each table I cannot. The example is oversimplified — Jacob Windsor, Apr 29 '14 at 22:25
You can and you must use a UNION if you want 3 rows. You have to explicitly define the columns of the result set. This same problem has been posed just a few days ago: http://stackoverflow.com/questions/23264116/join-tables-mysql/23264207#23264207 — Frazz, Apr 29 '14 at 22:31

score 0 · Answer 1 · answered Apr 30 '14 at 19:08

Thanks to Frazz for pointing out I could use UNIONS. I have researched into them and come up with this query.

SELECT stream.*, users.id AS me FROM users    
INNER JOIN followers ON users.id = followers.`user-id`
LEFT JOIN (
    SELECT `id`,`user-id`,`created_at`, `name`, NULL as status
    FROM events
    UNION ALL
    SELECT `id`,`user-id`, `created_at`,NULL AS name, `status`
    FROM status
) AS stream ON stream.`user-id` = `followers`.`follows-id`
WHERE users.id = 2

Now comes the process of converting it to an eloquent model...

Joining multiple tables with NULL values to avoid duplication

Example

Edit

1 Answers1