RSS

ASP.NET and SQL problem:

Please support our ASP.NET advertiser: Lunarpages ASP Web Hosting

Views: 1025 | Replies: 1 | Thread Tools Display Modes

•

Join Date: Sep 2007

Posts: 1,075

Reputation: SheSaidImaPregy is an unknown quantity at this point

Rep Power: 4

Solved Threads: 62

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

ASP.NET and SQL problem:

Nov 7th, 2007

hi, I have only done basic sql queries and need help on this major one trying to combine many queries into one.

What I need is to make the following queries into one:

Help with Code Tags

(Toggle Plain Text)

SELECT Username, LastLogin, DateCreated, Views, MainPic, Logged FROM Users WHERE UserID=?
SELECT COUNT(AlbumID) FROM ProfileAlbums WHERE UserID=?
SELECT AlbumID FROM ProfileAlbums WHERE UserID=? LIMIT 4
SELECT COUNT(FriendID) FROM ProfileFriends WHERE UserID=?
SELECT FriendUserID FROM ProfileFriends WHERE UserID=? LIMIT 12
SELECT MainPic FROM Users WHERE FriendUserID=? (comes from above statement)
SELECT COUNT(CommentID) AS CommentsCount FROM ProfileComments WHERE ReceiveUserID=?
SELECT PostUserID, DateCreated, Comment FROM ProfileComments WHERE CommentID=? LIMIT 25(comes from above statement)
SELECT UserName, MainPic FROM Users WHERE UserID=PostUserID LIMIT 25 (comes from above statement)

Obviously I need to Combine these and I can do most of it myself, but not into one query. One to two queries would be okay. What I have so far is below, and I will update as I go. But I do need help on this if anyone can.

Help with Code Tags

(Toggle Plain Text)

"SELECT Users.UserName, Users.LastLogin, Users.DateCreated, Users.Views, ProfileAlbums.AlbumID, ProfileFriends.FriendID, Users.MainPic, Users.Logged, 
(SELECT COUNT(AlbumID) FROM ProfileAlbums WHERE UserID=?) As TotalAlbums, 
(SELECT COUNT(FriendID) FROM ProfileFriends WHERE UserID=?) As CountFriends, 
(SELECT COUNT(CommentID) FROM ProfileComments WHERE ReceiveUserID=?) As CommentsCount 
FROM Users 
INNER JOIN ProfileAlbums ON ProfileAlbums.UserID=? 
INNER JOIN ProfileFriends ON ProfileFriends.UserID=? 
INNER JOIN ProfileComments ON ProfileComments.PostUserID=? WHERE UserID=?"

Now I am still missing these, which information for them is pulled with the above query:
SELECT PostUserName FROM ProfileComments WHERE PostUserID=..
SELECT MainPic FROM ProfileFriends WHERE FriendID=..

Oh, also so you should know, this is on MySQL v 5.0 or higher, and is being done in ASP.NET where I am binding them to repeaters. So it might not be the best solution. But I can work that out after I get the query

Thank you all!

Last edited by SheSaidImaPregy : Nov 7th, 2007 at 2:03 pm.

•

Join Date: Sep 2007

Posts: 1,075

Reputation: SheSaidImaPregy is an unknown quantity at this point

Rep Power: 4

Solved Threads: 62

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: ASP.NET and SQL problem:

Nov 8th, 2007

This is what I have so far, all for asp.net:

Help with Code Tags

(Toggle Plain Text)

repeater1: "SELECT UserName, LastLogin, DateCreated, Views, Logged, 
(SELECT COUNT(AlbumID) FROM ProfileAlbums WHERE UserID=?) As TotalAlbums, 
(SELECT COUNT(FriendID) FROM ProfileFriends WHERE UserID=?) As CountFriends, 
(SELECT COUNT(CommentID) FROM ProfileComments WHERE ReceiveUserID=?) As 
CommentsCount FROM Users WHERE UserID=? LIMIT 1"

repeater2: "SELECT AlbumID FROM ProfileAlbums WHERE UserID=? LIMIT 4"

repeater3: "SELECT MainPic As FriendMainPic FROM Users WHERE UserID IN 
(SELECT FriendID FROM ProfileFriends WHERE UserID=?) LIMIT 12"

repeater4: "SELECT ProfileComments.PostUserID, ProfileComments.DateCreated, 
ProfileComments.Comment, Users.UserName As PostUserName, Users.MainPic FROM 
ProfileComments INNER JOIN Users ON Users.UserID=ProfileComments.PostUserID WHERE 
ProfileComments.ReceiveUserID=? LIMIT 25"

I am not sure if it is possible to grab 4 queries with different limit sizes, not to mention if it is smart to bind 4 controls with tons of information rather than take the query hits. It's a profile section, much like myspace's. All by client's request.