| Author |
 Topic  |
|
|
dhinasql
Posting Yak Master
177 Posts |
 Posted - 07/17/2008 : 03:40:44
|
Hai Friends,
These are the 5 tables
usermgmt,profiledetails,tbl_lkaskandanswer,tbl_askandanswer,
Tbl_QuestionRating
usermgmt table contains following fields username,userid
profiledetails table contains following fields imageurl,userid
tbl_lkaskandanswer table contains following fields question ,questionid,creationdatetime,userid
tbl_askandanswer table contains following fields questionid,answer,userid
Tbl_QuestionRating table contains following fields questionid,rating,userid
I need questionid,question and no of answers corresponding to that particular question ,creationdatetime,userid,username,imageurl,rating
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 07/17/2008 : 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
177 Posts |
Posted - 07/17/2008 : 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 and
i just tried ,but i didn't get exact answer
Will you reply immediately |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 07/17/2008 : 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
177 Posts |
Posted - 07/17/2008 : 05:52:21
|
Thank you for your Query,
But i need Distinct questions and count of the answer. |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 07/17/2008 : 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
177 Posts |
Posted - 07/17/2008 : 06:18:25
|
Question |questionid | ownerid | Cdt | Username | Photourl | Answer | Rating
What is ASP? 2 27 2008-07-16 Dhina img.gif Active Server pages 1
What is ASP? 2 27 2008-07-16 Dhina img.gif It is a web page 1
This 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
177 Posts |
Posted - 07/17/2008 : 07:33:39
|
Why r u not replying to my question ? I need that query immediately So pls do that help |
|
|
|
Vadivu
Starting Member
India
31 Posts |
Posted - 07/17/2008 : 07:35:56
|
| do u want the answer column and rating column to be displayed? |
Edited by - Vadivu on 07/17/2008 07:39:54 |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 07/17/2008 : 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 it
Em |
|
|
|
dhinasql
Posting Yak Master
177 Posts |
Posted - 07/17/2008 : 07:49:29
|
| i need rating column and count of the answers for that particular question |
|
|
|
Vadivu
Starting Member
India
31 Posts |
Posted - 07/17/2008 : 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 =0
I have just reused and modified the query given by elancaster
|
Edited by - Vadivu on 07/17/2008 07:59:08 |
|
|
|
dhinasql
Posting Yak Master
177 Posts |
Posted - 07/17/2008 : 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
India
31 Posts |
Posted - 07/17/2008 : 08:03:51
|
| try to rectify it by adding a group by clause |
|
|
|
dhinasql
Posting Yak Master
177 Posts |
Posted - 07/17/2008 : 08:27:38
|
| It was Showing the same error |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/17/2008 : 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.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
group by l.Question, l.ownerid, l.creationdatetime, u.username, p.photourl, q.Rating
now if this doesn't give you correct results please see this
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
and give us the info that is said in the blog post or you won't get any further help
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
dhinasql
Posting Yak Master
177 Posts |
Posted - 07/17/2008 : 09:10:58
|
| Thank you ,now i got the answer. |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 07/17/2008 : 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! |
|
|
| |
Topic |
|
Joining 5 tables
