复杂的MySQL查询，多表 - 如何编写此关系查询？

Question

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?

Answer 1

You can use UNION in your query, but UNION requires the same number of columns (and same data type if I recall correctly) on both queries:

(SELECT media.id, media.accessKey, media.internalName, media.type, media.modified, users.username, users.id, media.created,
0 AS reposted
FROM media
LEFT JOIN users ON users.id = media.userId)
UNION
(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)