I'm having issues writing this query, it's an odd relation that I can't figure out. I'm wondering if it would be better to just use two mysql queries and merge the results with php?... Anyways.. so here we go.
Here's the tables we're using:
- media -
id
userId
accessKey
internalName
type
created
modified
- reposts -
id
userId
mediaId
created
- users -
id
username
Basically, what I want to do is get a result set of media items associated with the user who posted it, and then ALSO, in the same result set, include additional rows for media items that have been reposted, and then for reposted media items, instead of associating the media.userId of the media item for the username association, associate the reposts.userId as the username.
Here's a rough idea to illustrate, these two example queries below need to work as 1 to provide a combined result set.
SELECT media.*, users.username,
0 AS reposted
FROM media
LEFT JOIN users ON users.id = media.userId
SELECT media.id, media.accessKey, media.internalName, media.type, media.modified, users.username, reposts.userId, reposts.created,
1 AS reposted
FROM reposts
LEFT JOIN media ON media.id = reposts.mediaId
LEFT JOIN users ON users.id = reposts.userId
How would I go about doing this? Or would I be better off using 2 queries and merging the results with PHP?