Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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
0
pingeyeg
Asked:
pingeyeg
1 Solution
 
asafadisCommented:
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,
  1. What's the issue?
  2. What do all these tables mean (user_quest, answer, beyond_question, how_to)?
  3. 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.
0
 
igni7eCommented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
Mark WillsTopic AdvisorCommented:
Well, it seems to me that you are returning questions that have not been answered already. They will be the ones in the first part of the query.

But they will have NULL in the answer.* columns because of the left outer join.

So, it would seem to me that the answers.user and answers.qid should really be the columns from the user_question

What you could do is check the existing answers.* columns for NULL and populate accordingly.

e.g. (assuming MySQL syntax because of the LIMIT qualifier - and should probably be in your question zones or tags) that first part could be :

SELECT ifnull(answers.user,user_question.user) as User, ifnull(answers.answer,'No Answers Yet') as Answers, answers.qID, user_question.question, isnull(answers.date,user_question.date) as Posted_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

The above is a simple "guess" as to the names used in the user_question columns...

Either that, or, add a new (additional) first part of the query returning Questions asked without answers - basically the same link but add the where clause "Where answers.user is NULL" and you then use just the correct question columns in place of the answers columns.

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now