MYSQL选择子查询IF是否存在 [英] MYSQL Select Subquery IF exists
问题描述
我有3张桌子....用户,更新和撞车.
I have 3 tables ....users, updates and bumps.
我想返回使api请求供稿的用户以最新顺序更新供稿,其中包含所有必需的数据,以显示供稿中的每个状态. 我还需要包括该更新是否已由发出api请求的用户碰撞"(碰撞=喜欢在Facebook上的状态)
I want to return the user making the api request a feed updates in most recent order with all the needed data to display each of the statuses in the feed. I also need to include whether the update has been "bumped" by the user making the api request or not (bumped = liking a status on facebook)
简而言之,即使此查询的if计数区域中没有正确语法附近的地方,这也是我要尝试的操作:
In a nutshell this is what I am trying to do even though the if count area of this query is no where near correct syntax:
SELECT b.type,b.owner,b.update_img,b.ALBUM_ID,b.last_comment,a.uid, a.first_name, a.last_name, a.gender, a.thumb_img, b.msg_id, b.message, b.created,b.POST_PRIVACY,
(if count( SELECT * FROM BUMPS WHERE b.msg_id= BUMPS.MSG_ID_FK AND BUMPS.UID_FK=$loggedin_uid)>0 THEN 1 ELSE null) as 'isBumpedBool'
FROM users AS a, updates AS b
WHERE b.uid_fk = a.uid
AND b.type<>'FRIEND_RELATIONSHIP'
AND b.created<$time
AND b.type<>'FAMILIAR_RELATIONSHIP'
AND a.college='$college'
AND b.POST_PRIVACY<>'4'
AND b.POST_PRIVACY<>'5'
AND b.created>=$tstamp
ORDER BY b.created DESC
LIMIT 100
有什么想法吗?
推荐答案
添加一个LEFT JOIN以将所有这些结果JOINed到BUMPS上,如果值不存在则返回NULL
Add a LEFT JOIN to get all those result JOINed to BUMPS and return NULLs if the values are not there
SELECT b.type,
b.owner,
b.update_img,
b.ALBUM_ID,
b.last_comment,
a.uid,
a.first_name,
a.last_name,
a.gender,
a.thumb_img,
b.msg_id,
b.message,
b.created,
b.POST_PRIVACY,
(CASE WHEN bm.MSG_ID_FK IS NOT NULL THEN 1 ELSE NULL END) AS 'isBumpedBool'
FROM users AS a JOIN
updates AS b
ON b.uid_fk = a.uid
AND b.type<>'FRIEND_RELATIONSHIP'
AND b.created<$time
AND b.type<>'FAMILIAR_RELATIONSHIP'
AND a.college='$college'
AND b.POST_PRIVACY<>'4'
AND b.POST_PRIVACY<>'5'
AND b.created>=$tstamp
LEFT JOIN BUMPS as bm
ON b.msg_id= bm.MSG_ID_FK
AND bm.UID_FK=$loggedin_uid
ORDER BY b.created DESC LIMIT 100
这篇关于MYSQL选择子查询IF是否存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!