Jump to content

Complex Join


ionik

Recommended Posts

Ok I have a complex mysql statement....what it is supposed to do is select all users from the database,

then for each user select all videos that they have related to their Id then from the videos get the total number of views, avg rating etc etc, the query runs fine but the problem is is that when it runs through and starts getting the video information it only gets information from just one video in the Database....and not all of them...as some users will return with 23 videos and the tracking data is only for the 23rd video.....any reason why this would happen?

 

SELECT u.userName, u.userId, v.videoCount, c.commentCount, e.producerEarnings, w.viewCount, r.averageRating FROM user u LEFT JOIN ( SELECT videoId, userId, COUNT(*) AS videoCount FROM videos GROUP BY userId ) AS v USING (userId) LEFT JOIN ( SELECT videoId, COUNT(*) AS commentCount FROM videoComments GROUP BY videoId ) AS c USING (videoId) LEFT JOIN ( SELECT videoId, COUNT(*) AS viewCount FROM videoTracking GROUP BY videoId ) AS w USING (videoId) LEFT JOIN ( SELECT videoId, SUM(amount) AS producerEarnings FROM videoEarnings GROUP BY videoId ) AS e USING (videoId) LEFT JOIN ( SELECT videoId, ROUND( AVG(rating), 2 ) AS averageRating FROM videoRatings GROUP BY videoId ) AS r USING (videoId) WHERE userId < 100 ORDER BY u.userName DESC LIMIT 0,10

Link to comment
Share on other sites

SELECT u.userName, u.userId, v.videoCount, c.commentCount, e.producerEarnings, w.viewCount, r.averageRating

FROM user u

LEFT JOIN ( SELECT videoId, userId, COUNT(*) AS videoCount FROM videos GROUP BY userId ) AS v USING (userId) LEFT JOIN ( SELECT videoId, COUNT(*) AS commentCount FROM videoComments GROUP BY videoId ) AS c USING (videoId) LEFT JOIN ( SELECT videoId, COUNT(*) AS viewCount FROM videoTracking GROUP BY videoId ) AS w USING (videoId) LEFT JOIN ( SELECT videoId, SUM(amount) AS producerEarnings FROM videoEarnings GROUP BY videoId ) AS e USING (videoId) LEFT JOIN ( SELECT videoId, ROUND( AVG(rating), 2 ) AS averageRating FROM videoRatings GROUP BY videoId ) AS r USING (videoId) WHERE userId < 100 ORDER BY u.userName DESC LIMIT 0,10

Link to comment
Share on other sites

SELECT u.userName, u.userId, v.videoCount, c.commentCount, e.producerEarnings, w.viewCount, r.averageRating

FROM user u

LEFT JOIN (

    SELECT videoId, userId, COUNT(*) AS videoCount FROM videos GROUP BY userId ) AS v USING (userId)

LEFT JOIN (

    SELECT videoId, COUNT(*) AS commentCount FROM videoComments GROUP BY videoId ) AS c USING (videoId)

LEFT JOIN (

    SELECT videoId, COUNT(*) AS viewCount FROM videoTracking GROUP BY videoId ) AS w USING (videoId)

LEFT JOIN (

    SELECT videoId, SUM(amount) AS producerEarnings FROM videoEarnings GROUP BY videoId ) AS e USING (videoId)

LEFT JOIN (

    SELECT videoId, ROUND( AVG(rating), 2 ) AS averageRating FROM videoRatings GROUP BY videoId ) AS r USING (videoId)

WHERE userId < 100 ORDER BY u.userName DESC LIMIT 0,10

 

I have also tried creating th COUNT into a SUM using a sub query withing the subquery for each and producer the small result

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.