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
 Joining 5 tables

Author  Topic 

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-07-17 : 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

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.
Go to Top of Page

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 and
i just tried ,but i didn't get exact answer

Will you reply immediately
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-07-17 : 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



Go to Top of Page

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
Go to Top of Page

Vadivu
Starting Member

31 Posts

Posted - 2008-07-17 : 07:35:56
do u want the answer column and rating column to be displayed?
Go to Top of Page

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 it



Em
Go to Top of Page

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
Go to Top of Page

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 =0

I have just reused and modified the query given by elancaster
Go to Top of Page

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.
Go to Top of Page

Vadivu
Starting Member

31 Posts

Posted - 2008-07-17 : 08:03:51
try to rectify it by adding a group by clause
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-07-17 : 08:27:38
It was Showing the same error
Go to Top of Page

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.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!
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-07-17 : 09:10:58
Thank you ,now i got the answer.
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -