MySQL: Select by user, sort by any problem

Problem trying to sort by latest post when selecting by a specific user

[Simsi]

Hoping someone can help. I have a SELECT statement selecting questions and answers for a specific user ID. A question can have multiple answers.

The problem is, with ORDER BY on the answer timestamp field, because I am selecting by a specific user ID, it only knows the timestamp of the answers provided by that user. I want to sort it on the latest answer first, but an answer by anyone, not just the specific USERID I supply to the query.

Any ideas? Here is the code I have currently:

SELECT DISTINCT q.*, a.answer
FROM questions q, answers a
WHERE a.qid = q.id
AND a.uid = [$USERID]
ORDER BY a.tstamp DESC"

For the record, I then manipulate the output to display the question and the latest answer only. At the moment I can get it to show the question and the latest answer from my user, but not necessarily the latest answer period.

Many thanks,

Ian

[LifeinAsia]

Id sounds like you need to do 2 queries- one of questions by the user and one of answers by the user. Something like:
SELECT DISTINCT q.*, a.answer
FROM questions q, answers a
WHERE a.qid = q.id
AND a.uid = [$USERID]
UNION
SELECT DISTINCT q.*, a.answer
FROM questions q, answers a
WHERE a.qid = q.id
AND b.uid = [$USERID]

You'll need some further processing to filter out any duplicates (e.g., if a person posted an answer to his own question) and sort correctly.

[Simsi]

Hmm UNION. Thanks LiA - I'll have a play with that.