asked on

Query statement not outputting what I want

The query I have come up with is not doing exactly what I want it to do. My objective is to have all most recent questions from all corresponding tables be output to the screen in order of their date. The issue I'm having right now is I would like for the question, that has not been answered, to show on the screen with a comment below it stating it has not been answered, but if the question has been answered, I want the comment below it to state the last time it was updated. Placing the comment below it is not the issue; the issue is displaying the question properly. Below are screen shots and the query.

SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id uqID, user_question.make, user_question.model,  'type' 'A'
FROM user_question
LEFT JOIN answers ON user_question.id = answers.qID
UNION SELECT beyond_question.user, beyond_question.answer, beyond_question.id bID, beyond_question.question, beyond_question.date, NULL , NULL , NULL ,  'type' 'B'
FROM beyond_question
UNION SELECT how_to.user, how_to.answer, how_to.id hID, how_to.question, how_to.date, NULL , NULL , NULL ,  'type' 'H'
FROM how_to
ORDER BY DATE DESC 
LIMIT 6

Open in new window

Screen-shot-2010-01-01-at-10.51..png
Screen-shot-2010-01-01-at-10.51..png

asafadis

The description of your problem is very confusing.

Here,

"The issue I'm having right now is I would like for the question, that has not been answered, to show on the screen with a comment below it stating it has not been answered, but if the question has been answered, I want the comment below it to state the last time it was updated."

you state that your issue is placing a comment or a date depending on whether the question has been answered or not.

Then you say,

"Placing the comment below it is not the issue; the issue is displaying the question properly."

When describing the issue ("This issue I'm having...'), you say nothing about how improper the question is being displayed. In your screenshot, I can see the questions just fine.

So,

What's the issue?
What do all these tables mean (user_quest, answer, beyond_question, how_to)?
Could this be a PHP issue, and not a SQL issue?

Perhaps if you rephrase your problem and you "set the scene" for us, we could start helping you figure out a solution.

igni7e

SELECT answers.user, isnull(answers.answer,'This has not been answered'), answers.qID, user_question.question, answers.date, user_question.id uqID, user_question.make, user_question.model, 'type' 'A'
FROM user_question
LEFT JOIN answers ON user_question.id = answers.qID

UNION

SELECT beyond_question.user, isnull(beyond_question.answer,'This has not been answered'), beyond_question.id bID, beyond_question.question, beyond_question.date, NULL , NULL , NULL , 'type' 'B'
FROM beyond_question
UNION SELECT how_to.user, how_to.answer, how_to.id hID, how_to.question, how_to.date, NULL , NULL , NULL , 'type' 'H'
FROM how_to

ORDER BY DATE DESC

LIMIT 6

Raja Jegan R

Hope this is what you require:

1. TOP 1 record from First SELECT
2. TOP 1 records from second SELECT
3. TOP 4 records from Third SELECT

Just change the column names in ORDER BY clause before running the query..

SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id uqID, user_question.make, user_question.model,  'type' 'A'
FROM user_question
LEFT JOIN answers ON user_question.id = answers.qID
ORDER BY answers.date DESC
LIMIT 1
UNION 
SELECT beyond_question.user, beyond_question.answer, beyond_question.id bID, beyond_question.question, beyond_question.date, NULL , NULL , NULL ,  'type' 'B'
FROM beyond_question
ORDER BY some_column DESC
LIMIT 1
UNION 
SELECT how_to.user, how_to.answer, how_to.id hID, how_to.question, how_to.date, NULL , NULL , NULL ,  'type' 'H'
FROM how_to
ORDER BY how_to.date DESC 
LIMIT 4

Open in new window

Raja Jegan R

Syntax Mistake..

(SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id uqID, user_question.make, user_question.model,  'type' 'A'
FROM user_question
LEFT JOIN answers ON user_question.id = answers.qID
ORDER BY answers.date DESC
LIMIT 1)
UNION 
(SELECT beyond_question.user, beyond_question.answer, beyond_question.id bID, beyond_question.question, beyond_question.date, NULL , NULL , NULL ,  'type' 'B'
FROM beyond_question
ORDER BY some_column DESC
LIMIT 1)
UNION 
(SELECT how_to.user, how_to.answer, how_to.id hID, how_to.question, how_to.date, NULL , NULL , NULL ,  'type' 'H'
FROM how_to
ORDER BY how_to.date DESC 
LIMIT 4);

Open in new window

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial