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

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.

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

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

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

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-07-17 : 06:18:25

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 it

Em

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

I 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.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

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources