mysql - 嵌套的相关子查询(二阶)无法访问主查询(MySQL)

Question

我正在尝试做这样的事情

编辑 - 整个查询。

SELECT *
FROM ride
WHERE
(
    SELECT COUNT(*) FROM (
        SELECT DISTINCT mobile FROM contacts WHERE `contacts`.`user_mobile` =  '".$user_mobile."'
        UNION ALL
        SELECT DISTINCT mobile FROM contacts WHERE `contacts`.`user_mobile` = `ride`.`user_mobile`
    ) AS c
) > 1
AND ( 
    distance_geo('".$start_lat."', '".$start_long."', starting_lat, starting_long) < '".$start_radius."'
    OR id IN (
        SELECT rideid FROM routept WHERE 
        distance_geo('".$start_lat."', '".$start_long."', starting_lat, starting_long) < '".$start_radius."'
    )
)
AND ( 
    distance_geo('".$end_lat."', '".$end_long."', end_lat, end_long) < '".$end_radius."'
    OR id IN (
        SELECT rideid FROM routept WHERE 
        distance_geo('".$start_lat."', '".$start_long."', starting_lat, starting_long) < '".$end_radius."'
    )
)
AND availablity >= 1
AND start_time BETWEEN '".$start_after."' AND '".$start_before."'
AND start_time > NOW()
ORDER BY start_time ASC"

但是，我做不到。我得到了

Unknown column 'ride.user_mobile' in 'where clause'

我认为它不能在嵌套子查询中访问。我需要一个解决方法。我在这个 where 子句中有更多条件，但我只发布了麻烦的部分!

Answer 1

试试这个，让我知道

Select  contacts.user_mobile  // here you can add other columns
        , Count(*) as Total
From    ride 
  JOIN mobile On contacts.user_mobile = ride.user_mobile
Where contacts.user_mobile = ".$user_mobile."
Group By contacts.user_mobile
Having Total > 1

更新

上面的查询是你的基础，现在只需在 Where 子句中添加其他条件

Select  contacts.user_mobile  // here you can add other columns
        , Count(*) as Total
From    ride 
  JOIN mobile On contacts.user_mobile = ride.user_mobile
Where contacts.user_mobile = ".$user_mobile." 
    AND  distance_geo('".$start_lat."', '".$start_long."', 
            starting_lat, starting_long) < '".$start_radius."'
    OR ride.id IN (
        SELECT  rideid 
        FROM    routept 
        WHERE   distance_geo('".$start_lat."', '".$start_long."', 
                starting_lat, starting_long) < '".$start_radius."')
    AND  distance_geo('".$end_lat."', '".$end_long."', 
            end_lat, end_long) < '".$end_radius."'
    OR ride.id IN (
        SELECT  rideid 
        FROM    routept 
        WHERE distance_geo('".$start_lat."', '".$start_long."', 
                starting_lat, starting_long) < '".$end_radius."')
    AND ride.availablity >= 1
    AND ride.start_time BETWEEN '".$start_after."' AND '".$start_before."'
    AND ride.start_time > NOW()
Group By contacts.user_mobile
Having Total > 1
Order BY ride.start_time ASC"