Author |
Topic |
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 03:40:44
|
Hai Friends, These are the 5 tablesusermgmt,profiledetails,tbl_lkaskandanswer,tbl_askandanswer,Tbl_QuestionRatingusermgmt table contains following fields username,useridprofiledetails table contains following fields imageurl,useridtbl_lkaskandanswer table contains following fields question ,questionid,creationdatetime,useridtbl_askandanswer table contains following fields questionid,answer,useridTbl_QuestionRating table contains following fields questionid,rating,useridI need questionid,question and no of answers corresponding to that particular question ,creationdatetime,userid,username,imageurl,rating |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-07-17 : 04:06:27
|
So link them all by userid.Perhaps try yourself and then we can help if you get stuck. |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 05:25:06
|
select l.Question , l.questionid,l.ownerid ,l.creationdatetime, u.username ,p.photourl,a.answer ,q.Rating from tbl_lkaskandanswer l ,usermgmt u ,profiledetails p ,tbl_askandanswer a ,Tbl_QuestionRating q where l.ownerid=u.userid and l.ownerid=p.userid and l.questionid=a.questionid and l.deleted =0 ya,all the userid must be equal andi just tried ,but i didn't get exact answerWill you reply immediately |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-07-17 : 05:32:55
|
when you write it out with a 'proper' join it's easy to see you missed a join condition...select l.Question ,l.questionid ,l.ownerid ,l.creationdatetime ,u.username ,p.photourl ,a.answer ,q.Rating from tbl_lkaskandanswer l join usermgmt u on l.ownerid=u.userid join profiledetails p on l.ownerid=p.userid join tbl_askandanswer a on l.questionid=a.questionid join Tbl_QuestionRating q on .....where l.deleted =0 Em |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 05:52:21
|
Thank you for your Query,But i need Distinct questions and count of the answer. |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-07-17 : 05:58:52
|
i think you should show us some sample data and output so we can fully understand what you mean by...quote: I need questionid,question and no of answers corresponding to that particular question ,creationdatetime,userid,username,imageurl,rating
Em |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 06:18:25
|
Question |questionid | ownerid | Cdt | Username | Photourl | Answer | RatingWhat is ASP? 2 27 2008-07-16 Dhina img.gif Active Server pages 1What is ASP? 2 27 2008-07-16 Dhina img.gif It is a web page 1This is the result i am getting .What i need is ?The question should not repeat and no of answers for that questions |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 07:33:39
|
Why r u not replying to my question ? I need that query immediately So pls do that help |
|
|
Vadivu
Starting Member
31 Posts |
Posted - 2008-07-17 : 07:35:56
|
do u want the answer column and rating column to be displayed? |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-07-17 : 07:39:09
|
hmmm... i guess it just gets my back up when people abruptly 'demand' free help with their jobs 'immediately' now if you can just supply some sample data from your tables and the expected output i'm sure we'll all snap to itEm |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 07:49:29
|
i need rating column and count of the answers for that particular question |
|
|
Vadivu
Starting Member
31 Posts |
Posted - 2008-07-17 : 07:57:39
|
try something like this...select l.Question ,count(a.questionid) ,l.ownerid ,l.creationdatetime ,u.username ,p.photourl ,q.Rating from tbl_lkaskandanswer l join usermgmt u on l.ownerid=u.userid join profiledetails p on l.ownerid=p.userid join tbl_askandanswer a on l.questionid=a.questionid join Tbl_QuestionRating q on .....where l.deleted =0I have just reused and modified the query given by elancaster |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 08:01:51
|
I tried but it Showing Error like this.Column 'l.Question' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. |
|
|
Vadivu
Starting Member
31 Posts |
Posted - 2008-07-17 : 08:03:51
|
try to rectify it by adding a group by clause |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 08:27:38
|
It was Showing the same error |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-17 : 08:49:29
|
select l.Question ,count(a.questionid) ,l.ownerid ,l.creationdatetime,u.username ,p.photourl,q.Rating from tbl_lkaskandanswer l join usermgmt u on l.ownerid=u.useridjoin profiledetails p on l.ownerid=p.useridjoin tbl_askandanswer a on l.questionid=a.questionid join Tbl_QuestionRating q on .....where l.deleted =0group by l.Question, l.ownerid, l.creationdatetime, u.username, p.photourl, q.Rating now if this doesn't give you correct results please see thishttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxand give us the info that is said in the blog post or you won't get any further help_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
dhinasql
Posting Yak Master
195 Posts |
Posted - 2008-07-17 : 09:10:58
|
Thank you ,now i got the answer. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-07-17 : 09:18:29
|
dhinasql - How much are you getting paid to not know what you are doing?Demanding answers immediately is not going to get you what you want, especially as people here are helping for FREE! |
|
|
|