Complex Join

[ionik]

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

[fenway]

It's complex because it's all on one line... could you format that nicely?

[ionik]

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

[ionik]

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

[Barand]

In this subquery

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

 

you group by userid therefore you get one row for each user. Even if that user has 100 videos you can only get one video_id in that user's row.