我正在尝试查询4个表(当我查询前3个表时,它运行得很好),而且我的表结构看起来像,
1表-类别
catid catname
1 AAA
2 BBB2表格-问题
quid qtype
1 data
2
3 data3表-答案
ansid quid catid userid answer
1 1 1 1 test1
2 2 1 1 test2
3 3 2 1 test34表-关切事项
concern_id catid userid ansid
1 1 1 1
2 1 4 3现在我的查询是(有3个表)
SELECT category.catname,questions.quid,answers.catid,answers.ansid,answers.answer
FROM questions , answers ,category
where questions.qtype = 'data'
and questions.quid = answers.quid
and category.catid = answers.catid
and answers.userid = 1它给了我(这很好)
catname quid catid ansid answer
AAA 1 1 1 test1
BBB 3 2 3 test3现在,我希望在查询中包含第四个表,其结果应该如下所示
concern_id catname quid catid ansid answer
1 AAA 1 1 1 test1
null BBB 3 2 3 test3在这里,我被这个问题困住了。
发布于 2014-07-24 13:30:59
试试这个:
SELECT category.catname,questions.quid,answers.catid,answers.ansid,answers.answer
FROM
questions
INNER JOIN answers on (questions.quid = answers.quid )
INNER JOIN category cat on (category.catid = answers.catid)
LEFT JOIN concerns con on (concerns.userId = answers.userId and concerns.catid = answers.catid AND concerns.ansid = answers.ansid )
where questions.qtype = 'data'
and answers.userid = 1发布于 2014-07-24 13:31:21
您需要使用联接:
SELECT category.catname,questions.quid,answers.catid,answers.ansid,answers.answer
FROM category
join answers
on category.catid = answers.catid
join questions
on questions.quid = answers.quid
join concern
on concerns.userId = answers.userId and concerns.catid = answers.catid AND concerns.ansid = answers.ansid
where questions.qtype = 'data'
and answers.userid = 1发布于 2014-07-24 13:33:22
没有必要使用联接。MySQL将将连接版本编译为与此版本相同的结果。
我觉得你应该把它们结合在一起。
SELECT concern.concern_id, category.catname, questions.quid, answers.catid, answers.ansid, answers.answer
FROM questions, answers, category, concerns
where questions.qtype = 'data'
and concerns.ansid = answers.ansid
and questions.quid = answers.quid
and category.catid = answers.catid
and answers.userid = 1https://stackoverflow.com/questions/24935000
复制相似问题