从三个表中获取数据，但出现 SQL 语法错误

Question

我正在从三个表中获取数据：

$result = $this->db->query("
SELECT 
`meetings`.*,
`follow_up`.id as follow_up_id,
`follow_up`.comment as follow_up_comment,
`follow_up`.date as follow_up_date,
`follow_up`.time as follow_up_time,
SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = user_id,
(SELECT address FROM day_location WHERE `meetings`.assigned_to_id = user_id AND `follow_up`.date = date LIMIT 1) AS location_name
FROM meetings
LEFT JOIN follow_up ON `meetings`.id = `follow_up`.`meeting_id`
WHERE follow_up.`date` BETWEEN '{$fromDate_formated}' AND '{$toDate_formated}'
" . ($user_id > 0 ? " AND `meetings`.assigned_to_id = '{$user_id}'" : '') . "
ORDER BY `follow_up`.id DESC
");

错误：

发生数据库错误
错误号：1064

您的 SQL 语法有错误;请查看与您的 MySQL 服务器版本相对应的手册，了解在第 7 行 id = ' 的用户的"选择first_name、last_name、user_mobile useralt_mobile"附近使用的正确语法 选择

meetings.*，follow_up.id 作为follow_up_id，follow_up.comment 作为follow_up_comment，follow_up.date 作为follow_up_date，follow_up.time 作为follow_up_time，选择 first_name、last_name、user_mobile，useralt_mobile来自 id = user_id 的用户，(从 day_location 中选择地址，其中meetings.assigned_to_id = user_id 和follow_up.date = 日期限制 1( 作为会议中的location_namemeetingsfollow_up 上 .ID =follow_up。meeting_idfollow_up的地方。date在 '2018-10-01' 和 '2018-10-31' 之间，meetings.assigned_to_id = '1' 顺序按follow_up.ID DESC

你能帮忙吗？

Answer 1

为了获取用户信息，您应该使用联接

$result = $this->db->query("
SELECT 
`meetings`.*,
`follow_up`.id as follow_up_id,
`follow_up`.comment as follow_up_comment,
`follow_up`.date as follow_up_date,
`follow_up`.time as follow_up_time,
users.first_name, 
users.last_name, 
users.user_mobile,
users. useralt_mobile, 
(SELECT address FROM day_location WHERE `meetings`.assigned_to_id = user_id AND `follow_up`.date = date LIMIT 1) AS location_name
FROM meetings
LEFT JOIN follow_up ON `meetings`.id = `follow_up`.`meeting_id`
LEFT JOIN users on users.id = `meetings`.assigned_to_id 
WHERE follow_up.`date` BETWEEN '{$fromDate_formated}' AND '{$toDate_formated}'
" . ($user_id > 0 ? " AND `meetings`.assigned_to_id = '{$user_id}'" : '') . "
ORDER BY `follow_up`.id DESC");

Answer 2

你需要替换这个：

SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = user_id,

有了这个：

(SELECT first_name, last_name, user_mobile, useralt_mobile from users where id = user_id),