Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-10-24 : 15:57:25

Hi Friends,

I have three tables named as questions, answers, and category

In the Question table i have the below Field
QuestionId, QuestinTitle,CategoryId,UserId and postedDate

In the answer table i have the below field
Answerid, questionid,answeringuserid,answer

in the category table i have
id, category

Here i want to find what what are the questions are already answered and what are the questions are un answered..

Please send me the query.

Thanks in Advance friends

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-24 : 16:06:37

Show us what you have so far.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-10-24 : 16:15:42

Hi tkizer,

Thank you for your quick response,

I tried the below QUERY but i am getting the repeated rows

select * from questions q,Category c, answers a
where q.categoryid=C.Id and q.questionid Not IN
(Select questionid from answers) order by q.creationdatetime

Please help me in this regards
Thanks in advace

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-10-24 : 16:57:14

Hello Friends,

I am getting the repeated row please help me how to get my expected result.

Thanks in Advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-24 : 17:10:19

Show us sample data of your data and the expected output of the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 17:14:33

[code]
select * from
questions q left join Category c
on q.categoryid = c.id
left join answer a
on q.questionid = a.questionid
order by q.creationdatetime[/code]

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-10-25 : 00:21:43

Dear Friends,

I will show you some sample data and expected result

Questions table

QuestionId QuestinTitle CategoryId UserId creationdatetime
1 testquestion 8 250 8/8/2008
2 testquest1 9 21 8/9/2008
3 testststs 8 120 9/9/2008
4 testresull 10 250 6/6/2008

Answers Table

Answerid questionid answeringuserid answer
1 1 290 Hi i post answer
2 1 890 answers1
3 2 900 answewew

Category Table
id category
---------------------
8 education
9 commercial
10 general

Expected Output
----------------

ANSWERED QUESTION
-----------------

QuestionTitle Category creationtime numberofAnswer
testquestion Education 8/8/2008 2
testquest1 Commercial 8/9/2008 1

Please help me for getting my expected result

Thanks in Advance

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-25 : 01:35:53

try this


DECLARE @Questions table(QuestionId INT, QuestinTitle VARCHAR(100), CategoryId INT,
		UserId INT, creationdatetime DATETIME)
		
INSERT INTO @Questions	
SELECT 1, 'testquestion', 8, 250, '8/8/2008' UNION ALL
SELECT 2, 'testquest1', 9, 21, '8/9/2008' UNION ALL
SELECT 3, 'testststs', 8, 120, '9/9/2008' UNION ALL
SELECT 4, 'testresull', 10, 250, '6/6/2008'

DECLARE @Answers Table(Answerid INT, questionid INT, answeringuserid INT, answer VARCHAR(100))

INSERT INTO @Answers 
SELECT 1, 1, 290, 'Hi i post answer' UNION ALL
SELECT 2, 1, 890, 'answers1' UNION ALL
SELECT 3, 2, 900, 'answewew'


DECLARE @Category Table (id INT, category VARCHAR(100))

INSERT INTO @Category
SELECT 8, 'education' UNION ALL
SELECT 9, 'commercial' UNION ALL
SELECT 10, 'general'

/*
SELECT * FROM @Questions
SELECT * FROM @Answers
SELECT * FROM @Category
*/

SELECT DISTINCT Q.QuestinTitle
		, C.category
		, Q.creationdatetime
		, COUNT(A.answer) AS 'numberofAnswer'
		, CASE WHEN COUNT(A.answer) > 0 THEN 'Answere' ELSE 'Un-Answered' END AS 'Status'
FROM @Questions Q
INNER JOIN @Category C ON C.id = Q.CategoryId
LEFT JOIN @Answers A ON A.questionid = Q.questionid
GROUP BY Q.QuestinTitle, C.category, Q.creationdatetime
ORDER BY Q.creationdatetime

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-25 : 02:02:20

Try this It may helps you.

select distinct(QuestinTitle),Category,postedDate,a.ans as NumberofAnswers
from Question inner join category on categoryid=id
inner join answer on question.questionid=answer.questionid
inner join (select count(*) as ans,questionid from answer group by questionid)a
on Question.questionid=a.questionid

malay

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-10-25 : 02:09:14

Dear PeterNeo,

Thank you for your query, I want two different Queries,

1. Is for Answerd Questions
2. Is for Un Answered Questions

I dont want a status field here, Just i need two different queries
Thank you very much all your helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:15:20

[code]
SELECT q.QuestinTitle,
c.category,
q.creationdatetime,
COALESCE(a.NoOfAnswers,0) AS NoOfAnswers
INTO #Temp
FROM Questions q
INNER JOIN Category c ON c.id=q.Categoryid
LEFT JOIN (SELECT questionid,COUNT(answerid) AS NoOfAnswers
FROM Answers
GROUP BY questionid) a
ON a.questionid=q.Questionid
SELECT 'ANSWERED QUESTIONS'
SELECT * FROM #Temp WHERE NoOfAnswers>0
SELECT 'UNANSWERED QUESTIONS'
SELECT * FROM #Temp WHERE NoOfAnswers=0
DROP TABLE #Temp
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources