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:
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.
"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!