Member Avatar for SheSaidImaPregy

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!

  • 1 Contributor
  • 1 Reply
  • 90 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by SheSaidImaPregy
Member Avatar for SheSaidImaPregy

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.