CODEWITHSUNDEEP
phpmysql
pingpong
pingpong

Reputation: 1247

getting data from two tables for the same user?

I have two tables question and answers.

- questions{id,user_id,question,date}
- answers {id,q_id,user_id,answer, date}

I want to retrieve the questions and answers that have been inputted by the same user

 i.e. select all answer and all questions for ID=39 order by date DESC

and also after I have a query, and I'm wanting to while through the fetch array, and displaying the data, how can I distinguish whether it was a question or an answer, so I can display them correctly.

EDIT:

SELECT 'Q' AS
TYPE , q.question AS value, q.date
FROM questions q
WHERE q.user_id =39
UNION ALL SELECT 'A' AS
TYPE , q.question AS value, a.date
FROM answers a,questions q
WHERE a.q_id = q.id
AND
WHERE a.user_id =39
ORDER BY `date` DESC

im sorry but im trying to get the question that has been answered rather then the answer itself. i updated the sql and the database design on top, but i keep getting an error

Upvotes: 2

Views: 271

Answers (4)

mario
mario

Reputation: 145512

I would be lazy and use an UNION ALL as that just concatenates two tables:

SELECT *, NULL as answer FROM questions WHERE ID=39
UNION ALL
SELECT *, NULL as question FROM answers WHERE ID=39
ORDER BY date DESC

The trick for the later differantiation is to have a null field, or some other identifier.

Upvotes: 0

ThiefMaster
ThiefMaster

Reputation: 318808

As there doesn't seem to be a relation between questions and answers: Use separate queries.

Upvotes: -1

OMG Ponies
OMG Ponies

Reputation: 332791

Use:

  SELECT 'Q' AS type,
         q.question AS value,
         q.date
    FROM QUESTION q
   WHERE q.user_id = ?
UNION ALL
  SELECT 'A' AS type,
         a.answer AS value,
         a.date
    FROM ANSWER a
   WHERE a.user_id = ?
ORDER BY `date` DESC

UNION and UNION ALL are used to combine queries to return a single result set. UNION removes duplicates; UNION ALL does not remove duplicates and is faster than UNION because of this. But there needs to be the same number of columns in the SELECT clauses of all the SELECT statements in a UNION'd statement. And their data types have to match at each position.

To differentiate between answers and question values, the example defined static values "A" to stand for answers, and "Q" to stand for questions. Your application layer code can work off these to format the data as desired.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453990

SELECT 'Q' as `type`,
       id,
       user_id,
       question as quesion_or_answer,
       `date`
FROM   questions
WHERE  user_id = 39
UNION ALL
SELECT 'A' as `type`,
       id,
       user_id,
       answer as quesion_or_answer,
       `date`
FROM   answers
WHERE  user_id = 39
ORDER  BY `date`  DESC

Upvotes: 2

Related Questions