CODEWITHSUNDEEP
mysqljoin
Muzz
Muzz

Reputation: 687

How to get the results of 2 rows into one

I have a query that will only ever produce 2 rows. Each row contains one question and its matching answer. What I want to do is to have a single row with 4 columns. How can this be done? I can concat the values into a string but I'd rather not do this.

Can someone provide me with an example? This is what I'm after:

Q1              A1            Q2                 A2 
Some question   Some answer   Another question   Another answer

This is my query

SELECT question, answer FROM
user_question AS uq
Join question AS q ON q.question_id = uq.question_id

Upvotes: 0

Views: 40

Answers (2)

John Woo
John Woo

Reputation: 263723

how about using UNION?

like this:

SELECT iQuestion, iAnswer
FROM
    (SELECT Q1 as iQuestion, -- change to orig column name
            A1 as iAnswer,   -- change to orig column name
            questionID
    FROM    user_question INNER JOIN question
                ON user_question.question_id = question.question_id
    UNION
    SELECT  Q2 as iQuestion, -- change to orig column name
            A2 as iAnswer,   -- change to orig column name
            questionID
    FROM    user_question INNER JOIN question
                ON user_question.question_id = question.question_id) as iResult
WHERE   iResult = 'QUESTIONID HERE'

PS: Please post the SCHEMA of your table so that we would know the exact column names and the field you will be using in WHERE condition.

Upvotes: 0

Stephen Quan
Stephen Quan

Reputation: 25956

Sure you can, you just need to query the same table twice. You should use the AS keyword so you can be sure which version of the table is being used in the SELECT and WHERE portions:

SELECT a.question as q1,
       a.answer as a1,
       b.question as q2,
       b.answer as a2
FROM   user_question as a,
       user_question as b
WHERE  a.question_id = 1001
AND    b.question_id = 1002;

Upvotes: 1

Related Questions