Reputation: 111
I have two tables:
1) BlogAlbum:
Id AlbumName CoverPhoto CreatedDate Description
1 Onam noImage.jpg 2012-07-05 10:54:46.977 Onam is an ancient festival which still survives in modern times. Kerala's rice harvest festival and the Festival of Rain Flowers, which fall on the Malayalam month of Chingam, celebrates the Asura King Mahabali's annual visit from Patala (the underworld). Onam is unique since Mahabali has been revered by the people of Kerala since prehistory. According to the legend, Kerala witnessed its golden era during the reign of King Mahabali. The Brahma-Vaivarta Puranam explains that Lord Vishnu wante
2 Birds noImage.jpg 2012-07-05 11:02:48.667
3 Nature gold-fish.jpg 2012-07-05 11:03:36.503
2. BlogPhoto:
Id AlbumId PhotoName AddedDate
29 1 Photo0364.jpg 2012-07-05 11:01:18.270
30 1 Photo0380.jpg 2012-07-05 11:01:35.723
31 1 Photo0404.jpg 2012-07-05 11:01:47.717
32 1 Photo0404.jpg 2012-07-05 11:02:34.457
33 2 f.jpg 2012-07-05 11:03:03.300
34 2 g.jpg 2012-07-05 11:03:12.917
35 3 image005.jpg 2012-07-05 11:03:45.367
36 3 1.jpg 2012-07-05 11:03:57.837
37 3 2.jpg 2012-07-05 11:04:05.580
38 3 na.jpg 2012-07-05 11:04:17.337
39 3 gold-fish.jpg 2012-07-05 11:06:29.453
I wrote a stored procedure to list the details of BlogPhoto:
ALTER PROCEDURE [dbo].[BlogPhotoDetailsById]
(
@Id int
)
AS
BEGIN
SELECT [HRM_BlogPhoto].[Id]
,[HRM_BlogPhoto].[AlbumId]
,[HRM_BlogPhoto].[PhotoName]
,[HRM_BlogPhoto].[AddedDate]
,[HRM_BlogPhoto].[Title]
,[HRM_BlogPhoto].[ModifiedDate]
FROM [HRM_BlogPhoto]
WHERE [HRM_BlogPhoto].[Id] = @Id
END
What I want is that if BlogPhoto.PhotoName equals BlogAlbum.CoverPhoto, then in the above storedprocedure I need to display "true" and if not I need to display "false".
How could I modify the above stored procedure?
Upvotes: 1
Views: 99
Reputation: 107407
The below will display whether or not the BlogPhoto is a CoverPhoto of at least one BlogAlbum (noting that a photo could be on more than one album)
Edit
Bennor's point is taken. The below will avoid the nested loop, although note because of potentials for duplicate cover photos, you will need to add the distinct keyword.
ALTER PROCEDURE [dbo].[BlogPhotoDetailsById]
(
@Id int
)
AS
BEGIN
SELECT DISTINCT
[HRM_BlogPhoto].[Id]
,[HRM_BlogPhoto].[AlbumId]
,[HRM_BlogPhoto].[PhotoName]
,[HRM_BlogPhoto].[AddedDate]
,[HRM_BlogPhoto].[Title]
,[HRM_BlogPhoto].[ModifiedDate]
,CASE WHEN [blogalbum].CoverPhoto IS NOT NULL
THEN 'true'
ELSE 'false'
END AS [IsCoverPhoto]
FROM [HRM_BlogPhoto]
LEFT OUTER JOIN [blogalbum]
ON [blogalbum].CoverPhoto = [HRM_BlogPhoto].[PhotoName]
WHERE [HRM_BlogPhoto].[Id] = @Id
One caveat - for performance reasons, you should ensure that you have an index on [blogalbum].CoverPhoto if you go with this approach.
Upvotes: 1