How to form a single SQL statement in a datalist for a challenging and convoluted problem

Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message.

FROM | SUBJECT
| User Photo | |
| | Subject |
| User Name | |

Here is the list of the relevant 4 tables of my database and the relevant fields....

aspnet_Users table
UserId (used to link to Member table)
UserName

Member table
memberId (int - primary key)
UserId (guid - used to link to aspnet_Users table)
primaryPhotoId (int - used to link to Photo table)

Photo table
photoId (int - primary key)
photoUrl (string - path to file on local drive)

Message table
messageId (int - primary key)
fromMember (int - connects with memberId from Member table)
toMember (int - connects with memberId from Member table)
subject (varchar(max))

So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far....

SELECT aspnet_Users.UserName, Message.subject
FROM aspnet_Users, Member, Message
WHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember

Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.

And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained.... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol.

This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.

S2kDriver:

SELECT aspnet_Users.UserName, Message.subject
FROM aspnet_Users, Member, Message
WHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember

- Close...

You could simply add a subselect:

SELECT
aspnet_Users.UserName,
Message.subject,
(SELECT photoUrl FROM Photo WHERE photoID = Member.PrimaryPhotoID) AS MemberPhotoURL
FROM
aspnet_Users, Member, Message
WHERE
aspnet_Users.UserName = Profile.UserName
AND aspnet_Users.UserID = Member.UserID
AND Member.memberId = Message.toMember

You should read up on JOINS:http://www.singingeels.com/Articles/Understanding_SQL_SELECT_The_Data_You_Want.aspx

There's a few good SQL articles there... I think they're good because I wrote them.

|||

Thanks for your reply, Tim. And thanks for the article btw. I'm not familiar with JOINS and I'll try to get up to speed on them. My mind is mush right now from thinking too hard hehe.

However, I must apologize for making a mistake on my original post. I've edited and corrected it (not the SQL statement, but the explanations right after it). That SQL statement I have, only gets data based on the logged in user (aka the Message.toMember). The aspnet_Users.UserName I have in the SELECT statement returns the username of the logged in user (aka RECEIVER), NOT the SENDER that I had hoped for to use for the FROM column of my datalist. I somehow need to have the Message.toMember somewhere in that SQL statement which does the rest of what I hope for as written in my original post.

To recap in summary, I need to get the list of all messages sent to the logged in user (aka where Message.toMember = the logged in user). However, through all the smoke and clouds, I somehow need to get the senders' data as well (their username and photo, not the logged in user's, but the senders'). Seems like anything I try to do to add in that in extra functionality, I affect the original results. This seems like a "2-tier" or "2-pronged" search that needs to occur at the same time (maybe I'm not making sense and may seem like I'm talking out of my ass lol) and whatever is implemented, has to sync them together to arrive at the expected results.

Thanks in advance.

|||

Well, just to let you know... you've been doing a "JOIN" (of sorts) in your query...

Select * from table1, table2 <-- that's a "cross join"... that means that if there are 1000 records in table 1, and 1000 records in table 2... you'll get 1,000,000 records returned! (a combination of every row in table one and every row in table two multipled together).

Then if you add a "WHERE" clause to filter them out... that's basically like doing an INNER JOIN with an "ON" clause (actually, it's 100% the same):

Example: SELECT * FROM table1, table2 WHERE table1.SomeID = table2.RefID

IS THE EXACT SAME THING AS: SELECT * FROM table1 INNER JOIN table2 ON table1.SomeID = table2.RefID

(it just helps you to visually group together your "filter" with your table relationships).

OK... now on to your query... (actually, I'm going to post this and then write another reply because I don't have your query in front of me :P)

|||

S2kDriver:

aspnet_Users table
UserId (used to link to Member table)
UserName

Member table
memberId (int - primary key)
UserId (guid - used to link to aspnet_Users table)
primaryPhotoId (int - used to link to Photo table)

Photo table
photoId (int - primary key)
photoUrl (string - path to file on local drive)

Message table
messageId (int - primary key)
fromMember (int - connects with memberId from Member table)
toMember (int - connects with memberId from Member table)
subject (varchar(max))

- OK, let's see what we can do here :)

SELECT
Message.messageID,
Message.subject,
aspnet_Users.UserName AS SenderUserName,
Photo.photoUrl AS SenderPrimaryPhotoUrl
FROM
Message INNER JOIN aspnet_Users
ON Message.fromMember = aspnet_Users.UserId
INNER JOIN Member
ON aspnet_Users.UserId = Member.UserId
INNER JOIN Photo
ON Member.primaryPhotoId = Photo.photoId
WHERE
Message.toMember = @.DahUser

There... now, do you know how to add a paramter to you're query? (I called it "DahUser" to be silly of course)

|||

Hey Tim, thanks for your response and in helping me out with this.

Does the AS just rename a field? If so, I don't think I'll be needing that as I don't show the actual field names in my datalist that is shown to the user.

Yes, haha I got the DahUser. The parameter that I need to add is the Profile.UserName variable that automatically gets the username of the logged in user. So in that case, I would think the WHERE clause would need to be modified to be

WHERE aspnet_Users.UserName = @.DahUser (where DahUser equals Profile.UserName) AND blah blah blah (as shown below)

Also, regarding your first INNER JOIN, maybe I'm just ignorant, but I don't think that would work, sinceaspnet_Users.UserId ties in with Member.UserId andMember.memberId ties in with Message.fromMember. Not only would the values would not make sense to compare, but the former compares guids whereas the latter compares ints. So aMessage.fromMember = aspnet_Users.UserId would not work I think.

Here's my crack at it....

SELECT
Photo.photoUrl, aspnet_Users.UserName, Message.subject
FROM Message
INNER JOIN Member
ON Message.fromMember = Member.memberId
INNER JOIN aspnet_Users
ON Member.UserId = aspnet_Users.UserId
(I'm confused at this point.... can I join a table that isn't directly related to the preceding table? e.g. can I do an inner join with the Photo table right after this point, without joining again with the Member table?)
INNER JOIN Photo
ON Member.primaryPhotoId = Photo.photoId
WHERE
aspnet_Users.UserName = @.DahUser AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember

Tell me what you think and if there are flaws in my reasoning. Also, I noticed that you put inMessage.messageId in the SELECT clause. Just curious but did you put it in for a reason? I guess I may need it to further my options in the datalist, but for now and the sake of this example, I may not need it.

Important - note that there are two instances ofaspnet_Users.UserName - one in the SELECT clause and one in the WHERE clause - and both of them are NOT referring to the same person. The one in the SELECT clause is trying to get the username of the sender and the one in the WHERE clause is trying to determine whose inbox this is (aka the receiver of the message via theProfile.UserName object). Likewise, theaspnet_Users.UserId, theMember.UserId, and theMember.memberId values that are used in both the FROM and WHERE clauses are referring to different people in the same manner. I hope this won't confuse the SQL engine and get results I'm not expecting hehe. Whew, that was brain racking lol.

Thanks again.

|||

Btw, this INNER JOIN stuff makes visualizing what you want easier, like you said. The WHERE clause pinpoints ONLY what you're really looking for and most of the work is done in the FROM clause. Nice! I've also heard of like 5 other JOIN variations. Do you think they apply to my situation?

**UPDATE - This still does not work. I used the SQL query which I modified from yours. Theaspnet_Users.UserName in the SELECT still returns the username of the logged in user, NOT the sender. Just out of curiosity, I performed a test. There are 5 records in the Message table with toMember = logged in user and fromMember representing other users. Let's say the logged in user's toMember is '1'. I ran my modified SQL query and got NO results. It should have returned 5. Next, I changed 2 of those records, so that the fromMember equals '1' (e.g. the logged in user sent messages to himself - yea that doesn't make sense in reality but for sake of example and testing I did this). NOW, what happened was, I got 2 records in the result set. So what seems like is happening is, the SQL query seems to think that all the instances of variables such asaspnet_Users.UserName,aspnet_Users.UserId,Member.UserId, andMember.memberId are referring to the same person (e.g. the logged in user) and CANNOT differentiate between the sender and the logged in user. We probably need to put further logic in this right? Do you think we need a more sophisticated query or perhaps another set of instances of Table names to differentiate the both kinds of users (sender and receiver)? I have no clue lol. Need to get some aspirin.

|||

Ok, this is what I have so far. I tried creating another instance of the aspnet_Users and Member tables to try and differentiate the receiver (logged in user) and the sender. The '1' suffix means the sender and the '2' suffix means the receiver (logged in user). I did not write it with JOINS since I wasn't sure how the declaration of table instances is written.

SELECT
Photo.photoUrl, a1.UserName, Message.subject
FROM
aspnet_Users a1, aspnet_Users a2, Member m1, Member m2, Message, Photo
WHERE
a2.UserName = @.UserName AND a2.UserId = m2.UserId AND m2.memberId = Message.toMember AND (this part takes care of the receiver aka the logged in user)
Message.fromMember = m1.memberId AND m1.primaryPhotoId = Photo.photoId AND m1.UserId = a1.UserId (this part takes care of the sender)

Unfortunately, I'd just tried this statement in my application and it still does not work. It still seems the SQL statement can't differentiate between both types of users. Hmmm, what to do? I don't even know if I'm on the right track or not.

|||

Your JOINs are incorrect. Use ANSI standard joins as Tim suggested. If you have 3 tables T1, T2, T3 your SQL should look like this:

SELECT T1.col1, T2.col2, T3.col5FROM T1INNERJOIN T2ON T1.somecolumn = T2.someothercolumnINNERJOIN T3ON T2.somenewcolumn = T3.somedamncolumnWHERE T1.col4 = @.DahUser

On the other hand, if you wrote the old way, it is very easy to miss a join and not spot it.|||

Thanks for writing, Dinakar. Which one of my attempts are you referring to? For Tim's JOIN which I modified slightly, I was following his convention. Or are you referring to the one right above your post? Sorry, I'm not sure I follow.

Btw, how do you quote somebody here? Sometimes, it's difficult to know who someone is replying to.

Thanks.

|||

S2kDriver:

Ok, this is what I have so far. I tried creating another instance of the aspnet_Users and Member tables to try and differentiate the receiver (logged in user) and the sender. The '1' suffix means the sender and the '2' suffix means the receiver (logged in user). I did not write it with JOINS since I wasn't sure how the declaration of table instances is written.

SELECT
Photo.photoUrl, a1.UserName, Message.subject
FROM
aspnet_Users a1, aspnet_Users a2, Member m1, Member m2, Message, Photo
WHERE
a2.UserName = @.UserName AND a2.UserId = m2.UserId AND m2.memberId = Message.toMember AND (this part takes care of the receiver aka the logged in user)
Message.fromMember = m1.memberId AND m1.primaryPhotoId = Photo.photoId AND m1.UserId = a1.UserId (this part takes care of the sender)

Unfortunately, I'd just tried this statement in my application and it still does not work. It still seems the SQL statement can't differentiate between both types of users. Hmmm, what to do? I don't even know if I'm on the right track or not.

I was referring to this post.

When you click on reply, you can see a "Quote" button in the post that you are replying to.

|||

Ahh I see, thanks - can't believe I missed that quote button. On other BB forums I go to, the quote button is usually shown on the main post page before I hit reply, so I was looking in the wrong place.

As for that SQL statement I wrote... are you referring to the fact that I'm using 2 instances of some tables? The reason I tried that method was the SQL statement needs to fetch data about 2 different users and I was trying to differentiate them throughout the statement. I've already tried the previous examples and was getting incorrect results (SQL engine thought I was referring to one person only and I was getting the logged in user's info for the sender information when I should have been getting the actual senders' information). So I got a bit desperate and tried this multiple instance approach. I looked at that statement again and it seems to tie things together, the linking table between both the receiver and sender being the Message table. What am I missing here?

|||

Post some sample data from each of the tables and expected output. It will make it easier for us to understand what the requirement is.

|||

Sure will do that right now.

aspnet_Users
UserId UserName
800f1c5a-d6e6-448d-94e7-8b2a22a8a108 S2kDriver
4f92dc4e-0ce7-4cdb-81d2-78d8a8850052 SillyJoanna

Member
memberId UserId primaryPhotoId
1 800f1c5a-d6e6-448d-94e7-8b2a22a8a108 5
2 4f92dc4e-0ce7-4cdb-81d2-78d8a8850052 12

Message
messageId fromMember toMember subject
1 2 1 subject test
2 2 1 hahaha
3 1 2 testing testing
4 1 2 i have a headache

Photo
photoId photoUrl
5 ~/photos/s2kdriver.jpg
12 ~/photos/sillyjoanna.jpg

Ok, let's say I log in with "S2kDriver" credentials. I go to my INBOX. I expect to see a datalist presenting me with a list of all messages that were sent to me (aka Message.toMember = 1). As far as the presentation goes for each item in the datalist, just to recap from my very first post of this thread....

___________________________________________
| FROM | SUBJECT |
| Photo.photoUrl | |
| | Message.subject |
| aspnet_Users.UserName | |

So, what I expect to see is....

====================================
~/photos/sillyjoanna.jpg |
| subject test
SillyJoanna |
====================================
~/photos/sillyjoanna.jpg |
| hahaha
SillyJoanna |
====================================

I should be seeing 2 items show up in my datalist. Note that this is S2kDriver's INBOX, so all the listed messages have to be where Message.toMember = 1. However, I think the tricky part is where we have to now go switch and grab the senders' information (aka the senders' main photo and the senders' user name).

Thanks in advance, I appreciate the help.

|||

See if this helps:

--Create temp tables and sample dataDeclare @.aspnet_Userstable (UserIduniqueidentifier, UserNamevarchar(50))insert into @.aspnet_Usersselect'800f1c5a-d6e6-448d-94e7-8b2a22a8a108' ,'S2kDriver'unionallselect'4f92dc4e-0ce7-4cdb-81d2-78d8a8850052' ,'SillyJoanna'--select * from @.aspnet_UsersDeclare @.Membertable (memberIdint, UserIduniqueidentifier, primaryPhotoIdint)insert into @.Memberselect 1 ,'800f1c5a-d6e6-448d-94e7-8b2a22a8a108' ,5unionallselect 2 ,'4f92dc4e-0ce7-4cdb-81d2-78d8a8850052' ,12--select * from @.MemberDeclare @.Messagetable (messageIdint, fromMemberint, toMemberint, subjectvarchar(100))insert into @.Messageselect 1 , 2 , 1 ,'subject test'unionallselect 2 , 2 , 1 ,'hahaha'unionallselect 3 , 1 , 2 ,'testing testing'unionallselect 4 , 1 , 2 ,'i have a headache'--select * from @.MessageDeclare @.Phototable (photoIdint, photoUrlvarchar(100))insert into @.Photoselect 5 ,'~/photos/s2kdriver.jpg'unionallselect 12 ,'~/photos/sillyjoanna.jpg'--select * from @.Photo--Write the query to get the expected resultselect Au.UserName, P.photoUrl, Ms.subjectfrom @.Message MsJoin @.Member Mon Ms.fromMember = M.memberIdJoin @.aspnet_Users AUON AU.UserId = M.UserIdJoin @.Photo PON P.photoId = M.primaryPhotoIdWhere Ms.toMember = 1