join statement required

I have the following tables

tblFavourites
FavouriteSince datetime
UserCodeOwner int (the usercode of the user whose favouritelist this is)
UserCodeFavourite int (the usercode of the user who has been added to the favouritelist of usercodeowner)

EXAMPLE DATA
10/14/2006 7:32:30 PM 4 7
10/16/2006 11:24:01 PM 4 5
10/16/2006 10:55:08 PM 5 4

tblUserData
UserID uniqueidentifier
UserName nvarchar(50)
UserCode int

aspnet_Users
UserID uniqueidentifier
LastActivityDate datetime

Now I need a join statement that selects the fields
aspnet_Users.UserID,aspnet_Users.LastActivityDate,tblUserData.Username,tblUserData.UserCode,tblFavourites.FavouriteSince
for all tblFavourites.UserCodeFavourite where tblFavourites.UserCodeOwner=4

Could someone provide me with the join statement because I dont get it anymore :)

I think I'm a bit confused on your relationships... tblFavourites appears to have two columns that link to user records but its not clear where. Which table and column does UserCodeOwner and UserCodeFavourite link to? Do they link to the UserCode or UserID column of tblUserData or do they link to the aspnet_Users table instead?

Regards,

ZD

|||And I think I didnt explain that very well :)
The colums in the tblFavourites table link to the tblUserData.UserCode field.|||SELECT c.UserID,c.LastActivityDate,b.Username,b.UserCode,a.FavouriteSince
FROM tblFavourites a LEFT OUTER JOIN tblUserData b ON a.UserCodeOwner=b.UserCode
LEFT OUTER JOIN aspnet_Users c ON c.UserID=b.UserID
WHERE a.UserCodeOwner=4|||Thanks, but this statement selects the data for the UserCodeOwner (in this case 4).
I need the statement to select the data for all the UserCodeFavourite in which record the UserCodeOwner is 4.

Here's the content of tblUserData
UserID UserCode UserName

8ff3f242-2aab-499c-b0bf-f450969590ad4Jodie23e72b1a-b701-4b6d-9c39-83018dae8db15Jean71e1974f-8f1d-4e12-a0a6-7c8f3be87bc66Marie18ebec6d-adfa-4249-8c46-8daacca724e57Simon

Here's the content of tblFavourites:

FavouriteSince UserCodeOwner UserCodeFavourite

10/14/2006 7:32:30 PM4710/16/2006 10:55:08 PM5410/16/2006 11:24:01 PM4510/24/2006 10:07:46 PM79

And here's the content of aspnet_Users
UserID LastActivityDate
8ff3f242-2aab-499c-b0bf-f450969590ad 10/22/2006 10:32:44 PM
23e72b1a-b701-4b6d-9c39-83018dae8db1 10/16/2006 8:55:15 PM
230b9534-0d1e-4163-92f1-5f80927cac13 9/18/2006 7:53:49 PM
18ebec6d-adfa-4249-8c46-8daacca724e5 10/23/2006 9:55:45 PM

So in this case I want the LastActivityDate, UserName, FavouriteSince from UserCode 5 and 7
I need a select statement that for parameter usercode (value in this case 4) return:

LastActivityDate FavouriteSince UserName
10/16/2006 8:55:15 PM 10/16/2006 11:24:01 PM Jean
10/23/2006 9:55:45 PM 10/14/2006 7:32:30 PM Simon

I know this is tricky because a record in the aspnet_Users table is identified based on UserID, whereas in tblFavourites its on UserCode, so I need to get the matching UserCode and UserID from tblUserData in order to get the right lastactivitydate for a specific user.

Can you help me with the right statement?|||

SELECT u.LastActivityDate,f.FavouriteSince,ud.UserName

FROM tblFavourites f

JOIN tblUserData ud ON (ud.UserCode=f.UserCodeFavourite)

JOIN aspnet_Users u ON (u.UserID=ud.UserID)

WHERE f.UserCodeOwner=4

|||

If I understand your problem correctly, the following should work:

SELECT u.LastActivityDate
, f.FavouriteSince
, n.UserName
FROM aspnet_Users u, tblFavourites f, tblUserData n
WHERE n.UserID = u.UserID
AND n.UserCode = f.UserCodeOwner
AND n.UserCode = '4'

|||Sorry Motley - Didn't mean to step on your toes there. I didn't see your post until after I saved mine.|||Heh, no problem. As long as the question gets answered.|||

and that it is! :)