CODEWITHSUNDEEP
mysqlsqljoinleft-joinunion
2scope
2scope

Reputation: 23

Mysql union or join?

I have 2 tables, a questions and an answers table as so:

question : id, title, description, date, company_id
answers  : id, question_id, answer, date, company_id

I want a list of all questions asked whether they have answers or not and also all answers provided. I have done this without any trouble but the part of I am unsure on is how to provide the question title in the answer array as I want to display which question an answer relates to.

Currently I have this query: 

SELECT id, company_id, title, description, date, \'question\' as record_type 
        FROM `questions` WHERE company_id = 9
        UNION ALL 
        SELECT id, company_id, null as title, null as description, date, answer, question_id, \'answer\' as record_type 
        FROM `answers` WHERE company_id = 9
        ORDER BY date ASC

This almost provides me with what I want:

[0] => Array
    (
        [id] => 63,
        [company_id] => 9
        [title] => question 1
        [description] => test
        [date] => 2013-08-09 20:50:19
        [record_type] => question
    )

[1] => Array
    (
        [id] => 58
        [company_id] => 9
        [title] => 
        [description] => 
        [answer] => This is Bobs answer
        [question_id] => 63
        [date] => 2013-08-09 20:52:16
        [record_type] => answer
    )

The only difference is that I want to cross reference the question table and add the question title to the answer so that it looks like this:

[1] => Array
    (
        [id] => 58
        [company_id] => 9
        [question_title] => question 1
        [description] => 
        [answer] => This is Bobs answer
        [question_id] => 63
        [date] => 2013-08-09 20:52:16
        [record_type] => answer
    )

Can I amend my query or do I need another type of query with a left join perhaps?

Upvotes: 2

Views: 84

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

If you only want the question title of the answer and maintain the same result set structure, you can do an inner join, because your answers always have answers:

SELECT id, company_id, title, description, date, \'question\' as record_type 
FROM `questions` WHERE company_id = 9
UNION ALL 
SELECT a.id, a.company_id, q.title, q.description, a.date, a.answer, a.question_id, \'answer\' as record_type 
FROM `answers` a
INNER JOIN question q ON  q.id = a.question_id 
WHERE a.company_id = 9
ORDER BY a.`date` ASC

If you want to get the questions and whenever possible the answers in the same row you can do:

SELECT * FROM question q LEFT JOIN answers a ON a.question_id = q.question_id
WHERE q.company_id = 9
ORDER BY q.`date` ASC

Upvotes: 0

Ulrich Horus
Ulrich Horus

Reputation: 350

What you need is a join

Select * from answers left join question on answers.question_id = question.id;

Upvotes: 1

Related Questions