加入SQL表吗?

选择
问:问题是问题,
Q.日期\添加为日期,
Q.发布为发布,
q.用户ID作为用户,
qcat.name作为类别,
回答作为答案,
qans.publish作为anspublish发布
从
#_质量保证问题
#_ qa_类别xref为qcatref,
#_质量保证类别为QCAT,
#_ qa回答xref为qansref
#_质量保证回答为质量保证
在哪里?
q.qid=qcatref.qid
和qcat.catid=qcatref.catid
和q.quid=qansref.qid
和qans.aid=qansref.aid

假设qa_category_xref.id是fk to qa_答案。(假设没有uu qa_answer_xref table)。

select q.qid as [s/n], question, q.date_modify as [date], q.publish as publish
, user_id /*as no user table specified*/, c.name as category, answer, a.publish
from #__qa_question as q
join #__qa_category_xref as xref on q.qid = xref.qid
join #__qa_answer as a on a.aid = xref.id
join #__qa_category as c on c.catid = xref.catid

下面是10的开始部分(我假设user_id列包含用户名,但实际上可能还有另一个表):

SELECT q.question AS "Question",
       q.date_add AS "date",
       q.publish AS "publish",
       q.user_id AS "user name",
       c.name AS "category",
       a.answer AS "answer",
       a.publish AS "publish"
  FROM #__qa_question q
  LEFT OUTER JOIN #__qa_category_xref cx ON (cx.qid = q.qid)
  LEFT OUTER JOIN #__qa_category c ON (c.catid = cx.catid)
  LEFT OUTER JOIN #__qa_answer_xref ax ON (ax.qid = q.qid)
  LEFT OUTER JOIN #__qa_answer a ON (a.aid = ax.aid)

使用外部连接确保您可以看到每个问题,即使它没有答案或没有类别。

当然,如果(正如xref表所建议的那样)每个问题有许多答案和许多类别,那么您将看到每个问题有许多行,每个答案和类别组合有一行。