Query - 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

Query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-15 : 05:13:59

Hi Friends,

I have the table called rating,

Sample Data are

ratid, refid, userid, points
1 10 100 2
2 11 200 5
3 12 300 7

I need to check the userid and refid and have to return Points,and refid

Explanation 1 :
Where condition is where userid=200 and refid=11
means it will return
OUTPUT
Points , refid
5 11

Explanation 2 :
Where condition is where userid=100 and refid=10
means it will return
OUTPUT
Points , refid
2 10

Explanation 3:

Where condition is where userid=500 and refid=12
means it will not return any value
OUTPUT
Points , refid

BUT IN THIS SITUATION I need the output like below FORMAT

Points, refid
0 12

Please help me to find my expected output..

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-15 : 06:19:17

[code]SELECT Points,refid
FROM YourTable
WHERE userid=@userid
AND refid=@refid

IF @@ROWCOUNT=0
SELECT 0,@refid
[/code]

@refid and @userid are passed in values

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-15 : 06:47:07

Visakh16 thank you for your reply..

Its showing like the two table result, one is without row, and the second one is with value, but i can't access this from my front end..

Could you please alter, i need the result in the single table but this query displaying like a two tables...

Thanks in Advance

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-15 : 06:58:41

According to your senario it should return one record at time and if its true then your record will be returned by either query, first one or with IF. How can it return two result sets

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-15 : 07:12:16

Thank you liono,

it is not returning two result sets but it returning like the two query output..

Its showing the output like two table results..

I need in the single table

Please help me to get my expected output

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-15 : 07:31:19

IF EXISTS (SELECT Points,refid
FROM YourTable
WHERE userid=@userid
AND refid=@refid )

SELECT Points,refid
FROM YourTable
WHERE userid=@userid
AND refid=@refid

ELSE
SELECT 0,@refid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-15 : 11:58:33

[code]SELECT COALESCE(Points, 0),
COALESCE(refid, @refid)
FROM YourTable
WHERE userid = @userid
AND refid = @refid[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-15 : 13:14:18

quote:
Originally posted by Peso

SELECT COALESCE(Points, 0),
COALESCE(refid, @refid)
FROM YourTable
WHERE userid = @userid
AND refid = @refid

E 12°55'05.63"
N 56°04'39.26"

Not sure how this returns a record with 0 & refid when you have no matching records in table for passed in userid & refid value. i think thats what OP wants.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-16 : 03:28:27

Fixed.

SELECT	COALESCE(SUM(Points), 0),
	COALESCE(SUM(refid), @refid)
FROM	YourTable
WHERE	userid = @userid
AND	refid = @refid

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources