SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joining 5 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dhinasql
Posting Yak Master

194 Posts

Posted - 07/17/2008 :  03:40:44  Show Profile  Reply with Quote
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
3608 Posts

Posted - 07/17/2008 :  04:06:27  Show Profile  Reply with Quote
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

194 Posts

Posted - 07/17/2008 :  05:25:06  Show Profile  Reply with Quote
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

United Kingdom
1208 Posts

Posted - 07/17/2008 :  05:32:55  Show Profile  Reply with Quote
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

194 Posts

Posted - 07/17/2008 :  05:52:21  Show Profile  Reply with Quote
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

United Kingdom
1208 Posts

Posted - 07/17/2008 :  05:58:52  Show Profile  Reply with Quote

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

194 Posts

Posted - 07/17/2008 :  06:18:25  Show Profile  Reply with Quote

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

194 Posts

Posted - 07/17/2008 :  07:33:39  Show Profile  Reply with Quote

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

India
31 Posts

Posted - 07/17/2008 :  07:35:56  Show Profile  Reply with Quote
do u want the answer column and rating column to be displayed?

Edited by - Vadivu on 07/17/2008 07:39:54
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 07/17/2008 :  07:39:09  Show Profile  Reply with Quote
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

194 Posts

Posted - 07/17/2008 :  07:49:29  Show Profile  Reply with Quote
i need rating column and count of the answers for that particular question
Go to Top of Page

Vadivu
Starting Member

India
31 Posts

Posted - 07/17/2008 :  07:57:39  Show Profile  Reply with Quote
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
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 07/17/2008 :  08:01:51  Show Profile  Reply with Quote
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

India
31 Posts

Posted - 07/17/2008 :  08:03:51  Show Profile  Reply with Quote
try to rectify it by adding a group by clause
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 07/17/2008 :  08:27:38  Show Profile  Reply with Quote
It was Showing the same error
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 07/17/2008 :  08:49:29  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

194 Posts

Posted - 07/17/2008 :  09:10:58  Show Profile  Reply with Quote
Thank you ,now i got the answer.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 07/17/2008 :  09:18:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000