ASP.NET and SQL problem:

•

What is DaniWeb IT Discussion Community?

You're currently browsing the ASP.NET section within the Web Development category of DaniWeb, a massive community of 456,608 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,456 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

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

Thread Tools Display Modes

Views: 962 | Replies: 1

•

Join Date: Sep 2007

Posts: 1,058

Reputation: SheSaidImaPregy is an unknown quantity at this point

Rep Power: 4

Solved Threads: 61

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,058

Reputation: SheSaidImaPregy is an unknown quantity at this point

Rep Power: 4

Solved Threads: 61

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.