A complex SELECT for the guru's

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

SQL Server 2000 Forums

SQL Server Development (2000)

A complex SELECT for the guru's

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2001-07-30 : 16:08:07

There's nothing I hate more than asking for help or letting someone else do my homework, but I'm afraid I have reached a limit in my skill set and need some assistance with (what I would consider) a complex SELECT. This could be a long post, but I would truly appreciate any help that is offered.

Here is a peice of my original SP: (maximize to avoid wrap)
SELECT DISTINCT HostRoom.*,
(SELECT TOP 1 PhotoSrc FROM HostImage WHERE PhotoType = 'M' AND HostRoom.HostID = HostImage.HostID ORDER BY PhotoDate Desc) AS 'PhotoSrc',
(SELECT TOP 1 PhotoDate FROM HostImage WHERE PhotoType = 'M' AND HostRoom.HostID = HostImage.HostID ORDER BY PhotoDate Desc) AS 'PhotoDate',
(SELECT Host.CreateDate FROM Host WHERE HostRoom.UserID = Host.UserID) AS 'CreateDate',
(SELECT FeatureProfile FROM Host_Secondary WHERE HostRoom.UserID = Host_Secondary.UserID) AS 'Profile'
FROM HostRoom
INNER JOIN HostImage ON HostRoom.HostID = HostImage.HostID
INNER JOIN Host ON HostRoom.UserID = Host.UserID
INNER JOIN Host_Secondary ON HostRoom.UserID = Host_Secondary.UserID
WHERE PhotoSrc IS NOT NULL
ORDER BY HostRoom.OnlineNow Desc, OnlineStart Asc, OnlineStop Desc, LastChatMin Desc

Now, I've added another relationship (not defined as an actual relationship in SQL, rather in my head) between the tables above and a fourth. It is this
HostFeatureProfile (the pertinent fields: AccountID, FeatureProfile, X, Y, Z) (no PK intended)
Host_Secondary (the pertinent fields: AccountID, UserID, FeatureProfile) (PK on UserID)
HostFeatureProfile.AccountID = Host_Secondary.AccountID AND
HostFeatureProfile.FeatureProfile = Host_Secondary.FeatureProfile

Basically, I've assigned a feature profile at the user level and not at the HostRoom level. Each user can have up to 3 rooms, but since the profile assignment is at the user level, all rooms will use the profile assigned to that user.

Here's my question: How do I construct the JOIN (if any) to return not only the fields in the peice of my stored procedure that I put above but also the fields from HostFeatureProfile?

Edited by - kelleyb on 07/30/2001 16:31:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources