我有一个如下的sql查询,它根据问题表中是否存在值以及它们的section_id值是否在某个范围内来选择值,
SELECT action_id, action_type, action_details, action_int, action_time
FROM actions
WHERE user_id = '".$id."'
AND action_type = 'other'
AND ( action_details = 'random question'
OR action_details = 'review')
AND action_int IN (
SELECT id
FROM questions
WHERE section_id IN (2,3,4,5,6,7,8) )
我想扩展它,从问题表中提取第二个列值"section_id",并将其连接到结果中。
对此,最好的方法是什么?感谢
根据您在section_id中使用的in,我认为这个左联接应该有效:
SELECT a.action_id, a.action_type, a.action_details, a.action_int, a.action_time, q.section_id
FROM actions a left join questions q on a.action_int = q.id
WHERE a.user_id = '".$id."'
AND a.action_type = 'other'
AND a.action_details in('random question', 'review')
AND q.section_id IN (2,3,4,5,6,7,8);
通过查看您的代码,我假设actions.action_int字段与questions.id字段相关,我相信这就是您想要的:
SELECT actions.action_id, actions.action_type, actions.action_details, actions.action_int, actions.action_time, questions.section_id
FROM actions
JOIN questions
ON actions.action_int = questions.id
WHERE user_id = '".$id."'
AND action_type = 'other'
AND ( action_details = 'random question'
OR action_details = 'review')
AND action_int IN (
SELECT id
FROM questions
WHERE section_id IN (2,3,4,5,6,7,8) )