Join SQL Table?

Question

I have these tables.

Answer 1

Here's a starter for 10 (I'm assuming that the user_id column contains the user name, but there is probably another table in reality):

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)

Using outer joins ensures that you will see every question even if it has no answers or no categories.

Of course if (as suggested by the xref tables) there are many answers and many categories for each question then you will see lots of rows for each question, one for each answer and category combination.

Answer 2

Assuming #__qa_category_xref.id is FK to #__qa_answer. (No #__qa_answer_xref table assumed).

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

Answer 3

---

select
q.question as question,
q.date_add as date,
q.publish as publish,
q.user_id as user,
qcat.name as category,
qans.answer as answer,
qans.publish as anspublish
from
#__qa_question as q,
#__qa_category_xref as qcatref,
#__qa_category as qcat,
#__qa_answer_xref as qansref
#__qa_answer as qans
where
q.qid = qcatref.qid
and qcat.catid = qcatref.catid
and q.quid = qansref.qid
and qans.aid = qansref.aid

---