Question about Joins

Hi,

I have a stored Procedure called rpt_FundLipperRatings and i want to display a image which is store in the Database which a particular fundid doesnt exist. Below is the stored procedure that exist, I tried putting a case statement like totalreturnScore = CASE When TotalReturnScore IS NULL then 0 else TotalreturnScore END, but it doesnt work if the the fund doesnt exist in the database. So can some one please tell me how can i do it. and is it possible to put a where statement in a inner join

Like

Select * from fund f

Inner Join LipperLeaderPix ll on f.TotalReturnScore = ll.score where Condition

inner join .....

ALTER

PROCEDURE [dbo].[rpt_FundLipperLeaderRatings]

@.FundId

int

AS

SELECT

NumberOfYears

,

TotalReturnScore,

ll1.OverallBW TotalReturnImage,

ConsistentReturnScore

ll2.OverallBW ConsistentReturnImage,

PreservationScore,

ll3.OverallBW PreservationImage,

ExpenseScore,

ll4.OverallBW ExpenseImage

FROM

FundLipperLeader f

INNERJOIN LipperLeaderPix ll1ON f.TotalReturnScore= ll1.ScoreINNERJOIN LipperLeaderPix ll2ON f.ConsistentReturnScore= ll2.ScoreINNERJOIN LipperLeaderPix ll3ON f.PreservationScore= ll3.ScoreINNERJOIN LipperLeaderPix ll4ON f.ExpenseScore= ll4.Score

WHERE

f

.FundId= @.FundId

There are a number of ways you could handle this but I would have to see sample data in your tables to understand it.

You can always try using a LEFT OUTER JOIN instead of INNER JOIN on the two tables if you still want to return records that have NULL on the joined field

|||

Rossoneri,

Thanks for your reply and sorry for replying late.

The Fund Lipper Leader has the following Columns - FundId,,Numberof Years(0,3,5,10), DataValidDate, TotalReturnScore, ConsistentreturnScore, PreservationScore, ExpenseScore, addDate, LastChangeDate

All the score have data ranging from 0 - 5 and each score has a different picture associated with it.

The LipperLeaderPix has Columns which store Binary data(or the pictures) the columns are Score(0,1,2,3,4,5) OverallColor, ConsistentReturnColor, ExpenseColor, PreservationColor, TaxEfficineyColor, TotalReturnColor and OverallBW.

So the score is been associated with each picture. Is this information helpful or do u need more.

Regards,

Karen

|||

Rossoneri,

I tried running it using Left Outer Join but it didnt tried doing some like for some other sproc but i had to write a union to get to handle the non existent funds, but couldnt do it for this sproc.

Regards,

Karen