Better way of writing this statement

Question

Post reply

Better way of writing this statement

.Netter

SSCrazy

Points: 2476
More actions
December 6, 2014 at 6:58 pm

#299220

Hello,
I have a table called adverts and two tables called Sub Categories and child Categories some of the sub categories don't have child categories whilst others do.
So in the Adverts table the ChildCategoryId column could be null or it might have a value, depending on this I will return the relevant view to be displayed to the user.
So if the childCategoryId column has a value I power off that and return the ViewName that matches the childCategoryId from the ChildCategories table, if its null then I join on to SubCategory table and return the ViewName from there.
as follows my code looks like his
IF Exists(Select c.ViewName from Category.Child_Categories c
inner join Advert.Adverts a on c.ChildCategoryId =a.ChildCatId
where A.AdvertId = @ItemId)
Begin
Select c.ViewName from Category.Child_Categories c
inner join Advert.Adverts a on c.ChildCategoryId =a.ChildCatId
where A.AdvertID = @ItemId
End
ELSE
Begin
Select s.ViewName from Category.Sub_Categories s
inner join Advert.Adverts a on s.SubCategoryId =a.SubCatId
where A.AdvertId = @ItemId
End
But to me the above looks a bit OTT for trying to do such a simple thing hence why I thought I'll ask on here to see if anyone else has a better way of doing it
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Select coalesce(c.ViewName, s.ViewName) as ViewName

from Advert.Adverts a

left outer join Category.Child_Categories c

on c.ChildCategoryId =a.ChildCatId

join Category.Sub_Categories s

on s.SubCategoryId =a.SubCatId

where A.AdvertId = @ItemId

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);