Need to get information from another table.

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

Need to get information from another table.

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-27 : 13:33:42

ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100 ViewerUserNameID,
ViewedUserNameID,
ViewedDate,
PhotoID,
ProfileID,
p_Primary
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.[UserNameID],A.[ViewUserNameID] ORDER BY A.[TimeStamp] DESC) AS rn,
A.[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews
A.[ViewUserNameID] as ViewedUserNameID, ----- table tbl_ProfileViews
A.[TimeStamp] as ViewedDate, ----- table tbl_ProfileViews
B.[PhotoID], ----- table tbl_Photos
B.[ProfileID], ----- table tbl_Photos
B.[p_Primary] ----- table tbl_Photos
FROM tbl_ProfileViews A INNER JOIN tbl_Photos B
ON A.UserNameID = B.ProfileID
WHERE A.ViewUserNameID = @ViewedUsernameID
)t
WHERE rn=1
ORDER BY ViewedDate DESC

Need to also join the above with tbl_Profiles

=======================================================
I need to join the table tbl_profile in the above
query to get firstname and so forth

This is an example of another query.
ALTER PROCEDURE [dbo].[prc_Favorites_SelectByProfileID]
@ProfileID int
AS
SELECT
bb.FavoriteUserNameID as FavoriteUserNameID,
a.ProfileID as ProfileID,
a.UserID as UserID,
a.UserName as UserName,
a.FirstName as FirstName,
a.LastName as LastName,
a.HeadLine as HeadLine,
a.EmailAddress as EmailAddress,
a.GenderID as Gender,
b.Gender as GenderName,
a.SeekingID as Seeking,
c.Seeking as SeekingName,
a.SearchAgeStartID as StartAge,
a.SearchAgeEndID as EndAge,
a.Birthdate as BirthDate,
dbo.fnAge_GetAgeNumber(a.Birthdate) AS Age,
dbo.fnZodiac_GetZodiacName(a.Birthdate) AS ZodiacName,
ZipCode as ZipCode,
a.CountryID as Country,
d.Country as CountryName,
a.RegionID AS Region,
dbo.fnRegions_GetRegionName(a.RegionID, d.CountryID) as RegionName,
a.CityID AS CityName,
--------------------------------------------------------------
FROM [dbo].[tbl_Favorites] bb
INNER JOIN tbl_Profile a ON a.ProfileID = bb.FavoriteUserNameID
inner JOIN tbl_lookup_gender b ON a.GenderID = b.GenderID
inner Join tbl_lookup_seeking c ON a.SeekingID = c.SeekingID
inner Join CountryCodes d ON a.CountryID = d.CountryID

WHERE @ProfileID = bb.UserNameID AND
bb.FavoriteUserNameID = a.ProfileID

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-27 : 13:37:15

its just a matter of adding

INNER JOIN tbl_Profile pf
ON pf.ProfileID = A.UserNameID

on main query and adding required columns in select list inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-01-27 : 15:09:46

A few questions...

About this code...

SELECT ROW_NUMBER() OVER (PARTITION BY...

1) Is ROW_NUMBER always followed by the brackets?
2) What do the 'Over' and 'Partition By' clauses do?

And the code immediately following that:
ORDER BY A.[TimeStamp] DESC) AS rn,
A.[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews

Is the UserNameID being selected, or the result set ordered by it?

Cheers,

Jim

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-27 : 19:17:39

I tried the following:
USE [omegalove]
GO
/****** Object: StoredProcedure [dbo].[prc_ProfileViews_SelectByUserName] Script Date: 01/27/2011 18:09:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100
pf.FirstName,
ViewerUserNameID,
ViewedUserNameID,
ViewedDate,
PhotoID,
ProfileID,
p_Primary
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.[UserNameID],A.[ViewUserNameID] ORDER BY A.[TimeStamp] DESC) AS rn,
A.[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews
A.[ViewUserNameID] as ViewedUserNameID, ----- table tbl_ProfileViews
A.[TimeStamp] as ViewedDate, ----- table tbl_ProfileViews
B.[PhotoID], ----- table tbl_Photos
B.[ProfileID], ----- table tbl_Photos
B.[p_Primary] ----- table tbl_Photos
FROM tbl_ProfileViews A INNER JOIN tbl_Photos B
ON A.UserNameID = B.ProfileID INNER JOIN tbl_Profile pf
ON pf.ProfileID = A.UserNameID
WHERE A.ViewUserNameID = @ViewedUsernameID
)t
WHERE rn=1
ORDER BY ViewedDate DESC

Msg 4104, Level 16, State 1, Procedure prc_ProfileViews_SelectByUserName, Line 7
The multi-part identifier "pf.FirstName" could not be bound.

Any help would be awesome.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-28 : 13:17:21

Any help would be great.

quote:
Originally posted by visakh16

its just a matter of adding

INNER JOIN tbl_Profile pf
ON pf.ProfileID = A.UserNameID

on main query and adding required columns in select list inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-29 : 11:08:55

[code]
USE [omegalove]
GO
/****** Object: StoredProcedure [dbo].[prc_ProfileViews_SelectByUserName] Script Date: 01/27/2011 18:09:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100
FirstName,
ViewerUserNameID,
ViewedUserNameID,
ViewedDate,
PhotoID,
ProfileID,
p_Primary
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.[UserNameID],A.[ViewUserNameID] ORDER BY A.[TimeStamp] DESC) AS rn,
A.[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews
A.[ViewUserNameID] as ViewedUserNameID, ----- table tbl_ProfileViews
A.[TimeStamp] as ViewedDate, ----- table tbl_ProfileViews
B.[PhotoID], ----- table tbl_Photos
B.[ProfileID], ----- table tbl_Photos
B.[p_Primary], ----- table tbl_Photos
pf.FirstName
FROM tbl_ProfileViews A INNER JOIN tbl_Photos B
ON A.UserNameID = B.ProfileID INNER JOIN tbl_Profile pf
ON pf.ProfileID = A.UserNameID
WHERE A.ViewUserNameID = @ViewedUsernameID
)t
WHERE rn=1
ORDER BY ViewedDate DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources