SQL Server Query for getting result from two tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have two tables:

SQL

1. 
userInfo
(
ScreenName varchar(20),   (pk)
City varchar(50),
Gender varchar(6)
)

ScreenName	City	Gender	Age
Mitesh	Mumbai	Male	23
Varun	Delhi	Male	22
Ravi	Agra	Male	24
Karishma	Bangalore	Female	21
Purva	Hyderabad	Female	19

SQL

2.
friendList
(
senderScreen varchar(20),  (fk userInfo(ScreenName))
receiverScreen varchar(20),  (fk userInfo(ScreenName))
senderRated int,
receiverRated int
)

senderRated column stores the rating that receiver gave to sender
and
receiverRated column stores that rating that sender gave to the receiver.

senderScreen	receiverScreen	senderRated	receiverRated
Mitesh	Varun	5	4
Mitesh	Ravi	1	2
Varun	Karishma	2	NULL

I want a query to give the Output like:

ScreenName	Gender	City	Rating
Varun	Male	Delhi	3
Mitesh	Male	Mumbai	3
Ravi	Male	Mumbai	2
Karishma	Female	Bangalore	NULL
Purva	Female	Hyderabad	NULL

Posted 20-Jun-12 21:18pm

varun491

Updated 20-Jun-12 21:21pm

Prasad_Kulkarni

v3

Add a Solution

Comments

OriginalGriff 21-Jun-12 3:29am

How are you working out the rating? It isn't immediately obvious from the number examples you give.

varun491 21-Jun-12 3:32am

when you see the table 'friendList'
To calculate rating for Varun:
in first row Varun is in receiver column so i check the value in receiverRated for 1st row . i.e 4

Then in third row varun is the sender so I check the rating for varun in SenderRated column for this row . i.e 2

Then I add the ratings and divide by the count of the rates
i.e. 4+2/2

varun491 21-Jun-12 8:54am

not solved yet..
can anyone help.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ashriv · Answer 1 · 2012-06-20T21:28:00

Solution 1

use concept of 'Join' on your table.

Posted 20-Jun-12 21:28pm

ashriv