April 25, 2015 at 5:35 am
Hello,
I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?
The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join
My photos table is constructed as follows:
CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,
[DateCreated] [datetime] NOT NULL,
[DateDeleted] [datetime] NULL,
CONSTRAINT [PK_User.User_Photos] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
And this is my current proc
Select p.Id, p.Username, c.CountryName + ', ' + s.StateName + ', ' + c1.CityName [Location],
p.LoggedIn, p.LastLoggedIn, p1.PhotoId
from [User].User_Profile p
Inner join [User].User_Address a on p.Id = a.UserId
Inner join [Location].Country c on a.Country = c.Id
inner join [Location].States s on a.[State] = s.Id
inner join [Location].Cities c1 on a.City = c1.Id
Left join [User].User_Photos p1 on p.Id = p1.UserId
where p1.IsProfilePic = 1
Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck so any help would be appreciated.
April 25, 2015 at 7:41 am
The problem is in your WHERE clause. You're filtering for the profile picture, but if the outer join doesn't return a row, the value is NULL. The NULL means the row won't match your filter, so the row isn't returned. If you remove your WHERE clause, you should get your row back.
April 25, 2015 at 7:58 am
I need the where statement to specify the profile picture because the user can upload ten pictures if I remove the where statement I get x amount of records per user depending on how many photos that user has linked to him/her
April 25, 2015 at 8:38 am
You could add an "OR p1.IsProfilePic IS NULL" to your WHERE clause. However, because your User_Photos.IsProfilePic column can be NULL, this could pose a problem when the User_Photos table returns more than 1 matching row.
I don't know what else your procedure is doing, but there's probably more work you need to do to get everything about the user. If this is the case, you could divide up the work by creating a temp table and populating it with the base user information. Then fire an UPDATE from the photos table to define your image. Then you could do whatever else you need to do to get your return table done. This would involve separate queries and I don't know if the complexities of what you have to do necessitate a temp table, but I figured I'd throw it out there so you could consider it in the larger context.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply