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.

Complex Query Help.. Please :)

Hello group,

First time post..

I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..

Here are the tables.

evaluations - holds a record for each evauation that is created: evalID, evalName

fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)

fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average

fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID

submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID

eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data

Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.

an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as

with any luck this explanation makes sense and the problem will be easy to solve..

:rolleyes:

any replys are greatly appreciated..

Thanks
Willok.. here is some clarification..

I started thinking about the first post and decided that much of that information was probably not needed..

Here is the sql i'm workin with so far..

SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText

my question boils down to this..
regarding the rows in the data table..

can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.

anybody?

Am i on the wrong track?

Am i totally lost?

have i lost everybody else??

thanks
ws
-|||You are right on track, good job.
:D|||me again...

I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..

here is some example data from the data table..

intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28

intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.

make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to

Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..

any help is greatly appreciated.
If you need more info.. please let me know..

thanks
will