我有一个这样的问题:

select tractions.id,tractions.expired,tractions.succ,tractions.cradit_end_date,
                      tractions.user_id,users.family,tractions.coupon_code,users.username,traction_details.title,
                traction_details_sub.title as sub_title,tractions.coupon_parent,tractions.coupon_id,tractions.coupon_property_id
                from tractions,traction_details,traction_details_sub,users
                  WHERE
                  tractions.app='mobile'
                  AND tractions.succ = 1
                  AND tractions.user_id=$user_id
                  AND tractions.id = traction_details.tr_id
                  AND tractions.id = traction_details_sub.tr_id
                  AND tractions.user_id = users.id

现在,tractions中的一些记录在tr_id表中没有任何traction_details_sub
如何检查traction_details_sub表是否有tr_id然后连接这些表?

最佳答案

切换到ANSI联接(它们在很长一段时间内几乎是普遍可用的)并对可能没有记录的表使用外部联接:

SELECT
    t.id
,   t.expired
,   t.succ
,   t.cradit_end_date
,   t.user_id
,   u.family
,   t.coupon_code
,   u.username
,   d.title
,   s.title as sub_title
,   t.coupon_parent
,   t.coupon_id
,   t.coupon_property_id
FROM tractions t
LEFT OUTER JOIN traction_details d ON t.id = d.tr_id
LEFT OUTER JOIN traction_details_sub s ON t.id = s.tr_id
JOIN users u ON t.user_id = u.id
WHERE t.app='mobile' AND t.succ = 1 AND t.user_id=$user_id

请注意,此语法如何将表从FROM子句移到单独的联接中,并使用LEFT OUTER JOIN指定具有可选行的表。还要注意WHERE子句的最后三个条件是如何变成相应联接的ON条件的。最后,注意使用表别名(即t表示tractionsd表示traction_detailss表示traction_details_sub,以及u表示users)来缩短查询。

07-24 09:38
查看更多