complex SQL select query

Hi all

I im trying to write a SELECT query to display a set of my logged in user's 'Friends'. Although the way that i have designed my tables means that its very complex, and im hoping someone out there can tackle it!

To start ill show you how i contruct friends:

Friends

FriendshipID Incrementing PK
InviteeID Unique UserID of person who offered the friendship link
InvitedID Unique UserID of person who was invites
ApprovedBInvitee True/False - sets to 'True' by default (probably isnt needed come to think of it)
ApprovedByInvited True/False/Declined - an nvarchar

Next, I have my UserDetails table:

UserDetails

UserID Unique UserID PK
UserName Unique Username (foreign key from aspnet_Users as created by aspnet_regsql.exe)
Avatar Integer which represents an image name in a photos folder

So, on the myFriends.aspx i firstly set an invisible label's text property to the unique UserID of the logged in user. This gives me a control paremater for the select statement.

The information I want to display is just the UserName and Avatar of all users who are friends with the logged in user.

I know that to get the records where the logged in user is either that Invited or the Invitee, I do this:

WHERE (@.loggedInUser = Friends.IniteeID)OR (@.loggedInUser = Friends.InvitedID)

(that will show the logged in user as his own friend but i dont mind that)

After that I am stuck more or less... it seems to become very complex... maybe i need 2 queries?

If anyone can help i would be very very grateful

This is actually a very simple query... it may seem a bit complex because you join back twice on the user class... actually, you don't *have* to do that... there are many ways to accomplish this.

SELECT
friendUsers.UserName,
friendUsers.Avatar
FROM
dbo.UserDetails u INNER JOIN dbo.Friends f
ON u.UserID = f.InviteeID OR u.UserID = f.InvitedID
INNER JOIN dbo.UserDetails friendUsers
ON friendUser.UserID = f.InviteeID OR friendUser.UserID = f.InvitedID
WHERE
u.UserID = @.loggedInUser

That should work.

|||

Another way to do it would be this:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = InviteeID OR UserID = InvitedID)

Believe it or not, those are the same query.

|||

Nullable:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = InviteeID OR UserID = InvitedID)

I forgot one more piece to filter down by the current user:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

There :)

|||

Hi Nullable

Thanks for the rsponse, you obviously have more skills with sql than me!

I have tried the corrected second query:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

There is only one friends entry at the moment, one where the logged in userid will be the InvitedID (although that will not always be the case of course)

...that query is returning the Avatar and UserName of that user - the logged in one - rather than those of his friend. We need to stick a WHERE ApprovedByInvited = 'True' too, but i think i can manage that.

Do you know why we are getting the wrong user details?

Thanks again

|||I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.|||

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

I'll look into the query again to see where I crossed wires :)

|||

Heh, my "correction" to my earlier query was done in much haste and not thought out :) ... it was close, but not quite right:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHEREUserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

That is forcing only the current user... which was pretty dumb

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID != @.loggedInUser
AND (UserID = InviteeID OR UserID = InvitedID) AND (@.loggedInUser= InviteeID OR @.loggedInUser= InvitedID))

To read that in English you would say "Give me the UserName and Avatar FROM the UsersDetails Table WHERE the user that I'm looking at is part of the following list: (Give me all Users who are linked in the friend table WHERE either the user is the Invitee OR the user is the Invited AND the loggedInUser is an Invitee OR the loggedInUser is the Invited)"

Got it? Good :) (Please make sure to mark one of these posts as the answer when you're done so that I know this issue has been resolved.)

Peace,

|||

Nullable:

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

- Sorry to correct you like that, I don't mean to seem rude, so here is a quick explaination into why I corrected you.

SELECT

c.*FROM dbo.SysColumns cINNERJOIN dbo.SysObjects oON c.id= o.id
-- Subtree Cost : 0.0317435

SELECT

*FROM dbo.SysColumns cWHERE idIN(SELECT idFROM dbo.SysObjects)
-- Subtree Cost : 0.0317125These two queries will return the EXACT same result set... but the one with the JOIN is actually slightly more expensive (and takes longer) to run... Do you know why? Well, to put it very simply and I will probably be "corrected" on this explaination... but here goes: The RESULTS of the query were only from the SysColumns table... so joining the two (thereby forcing SQL to have to ORDER the SysObjects table by ID to do it's cross streaming) is more expensive than the second query which only needed to get the list of IDs (in any order) from the SysObjects table.

As a punishment for your crime, you must go tohttp://www.SingingEels.com and spread the word!

|||Thank you nullable and French Duke, I must try to reproduce this myself with varying amounts of test data.|||

Hey Timothy

Thanks bro, thats done the job just nicely. Marked you up

|||

Nullable:

Nullable:

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

- Sorry to correct you like that, I don't mean to seem rude, so here is a quick explaination into why I corrected you.

SELECT

c.*FROM dbo.SysColumns cINNERJOIN dbo.SysObjects oON c.id= o.id
-- Subtree Cost : 0.0317435

SELECT

*FROM dbo.SysColumns cWHERE idIN(SELECT idFROM dbo.SysObjects)
-- Subtree Cost : 0.0317125These two queries will return the EXACT same result set... but the one with the JOIN is actually slightly more expensive (and takes longer) to run... Do you know why? Well, to put it very simply and I will probably be "corrected" on this explaination... but here goes: The RESULTS of the query were only from the SysColumns table... so joining the two (thereby forcing SQL to have to ORDER the SysObjects table by ID to do it's cross streaming) is more expensive than the second query which only needed to get the list of IDs (in any order) from the SysObjects table.

As a punishment for your crime, you must go tohttp://www.SingingEels.com and spread the word!

Here's one article that I could find with peformance issues with IN:http://support.microsoft.com/kb/829205|||

Just recollected that the queries work differently if you have duplicate records in the subquery table. If your subquery has more records (like a 1-many relationship) doing a JOIN will return multiple records where as an IN might return only one record.