mysql – 必要SQL人人来举办伟大的查询

我有几个表,我试图获取一些数据,我很是靠近,但不能完全告竣协议.

我有以下表格：

>勾当
>用户
>伴侣
> USER__FRIEND
> EVENT__INVITATION

USER和FRIEND通过USER__FRIEND表(包括USER_ID和FRIEND_ID字段)链接

EVENT__INVITATION将EVENT与FRIEND相干联(它有EVENT_ID和INVITEE_ID)

我想把全部变乱都放在那边：

>我是EVENT建设者($myUserID = EVENT.CREATOR_ID)
>可能我被约请介入勾当($myUserID = EVENT__INVITATION.INVITEE_ID)
>或我的一个伴侣是EVENT的建设者($myUserID = USER__FRIEND.USER_ID AND EVENT.CREATOR_ID IN(我的伴侣列表))
>可能我的一个伴侣被约请介入勾当($myUserID = USER__FRIEND.USER_ID和EVENT__INVITATION.INVITEE_ID IN(我的伴侣列表))

其他参数尚有一些其他WHERE前提,但我想我可以本身办理这些题目.

此刻我独一能让它事变的要领就是行使UNION,我以为它必需是一个警员,假如我有更好的排骨,我可以绕过它.

以是,题目是,这可以通过一个不行使UNION的便宜查询来完成吗？

这是我到今朝为止所做的统统,除了我约请的FRIENDs的EVENT之外,它完成了全部工作(23是在这种环境下传入的userID)：

SELECT e.*
FROM event e
LEFT JOIN event__invitation ei ON ei.event_id = e.id
LEFT JOIN user__friend uf ON uf.friend_id = ei.invitee_id
LEFT JOIN friend f ON f.id = uf.friend_id
WHERE (ei.invitee_id = 23 OR e.creator_id = 23 OR uf.user_id = 23 OR f.user_id = e.creator_id)
AND e.start_time >= 1348000000

这是UNION的查询：

SELECT e.* FROM event e
INNER JOIN event__invitation ei ON ei.event_id = e.id
INNER JOIN user__friend uf ON uf.friend_id = ei.invitee_id
WHERE (e.creator_id = 23 OR ei.invitee_id = 23 OR uf.user_id = 23)
UNION
SELECT e1.* FROM event e1
WHERE e1.creator_id IN (
    SELECT f1.user_id FROM friend f1
    INNER JOIN user__friend uf1 ON uf1.friend_id = f1.id
    WHERE uf1.user_id = 23
    AND f1.user_id IS NOT NULL
);

查询尚有更多内容使得UNION的行使不受接待.我有一个伟大的trig计较,我在主选择中举办,并按该值对功效举办排序.我想也许搅散功效集.

感谢你的辅佐！！

-- take only distinct events
SELECT DISTINCT e.*
-- start with the event
FROM event e
-- expand to include all invitees and their user_friend info
LEFT JOIN event__invitation ei 
    ON ei.event_id = e.id
LEFT JOIN user__friend invitee
    ON invitee.friend_id = ei.invitee_id
-- now we join again to user_friend to get the friends of the invitees/the creator
LEFT JOIN user__friend invitedFriend
    ON invitedFriend.user_id = invitee.user_id
        OR invitedFriend.user_id = e.creator_id
-- finally we match on whether any of these friends of friends are myself
LEFT JOIN friend myselfAsAFriend
    ON myselfAsAFriend.id = invitedFriend.friendId
        AND myselfAsAFriend.userID = 23
WHERE 
(   
    -- (1) I am the creator of the event
    e.creator_id = 23 
    -- (2) I am invited to the event
    OR invitee.user_id = 23 
    -- (3 and 4) for this join to match a friend of mine must be invited or the creator
    OR myselfAsAFriend.id IS NOT NULL 
)
AND e.start_time >= 1348000000

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!