[SOLVED] Complex Left Join and Counting...

[ionik]

OK!

 

What i need to do is I have 2 MySQL Tables one holds information for channel data and another holds the video data tracking data the tables are linked between the channelId's the thing i need to do

is sort out the channels by the videos that have the highest rating and the rating are stored in a new row for each one here is a graph to better explain it

 

VideoTracking

---------------------------

videoId | channelId | rating

---------------------------

    2    |      1      |    2

---------------------------

    2    |      1      |    3

---------------------------

    1    |      3      |    5

---------------------------

    1    |      3      |    1

---------------------------

 

So as you see I need to add all of the values for videos ratings and then sort by that how would i DO this?

[ionik]

the only problem i have with this right now is counting the value of each rating for the videos....how can this be done?

[ionik]

NEVERMIND I solved the problem........here is the sql statement just incase anyone else runs into this problem

 

SELECT DISTINCT ch.channelId, ch.name, CEIL(SUM( vr.rating ) / COUNT(vr.rating)) as rating FROM '.CHANNELS.' ch RIGHT JOIN '.VIDEO_RATINGS.' vr ON vr.channelId = ch.channelId GROUP BY ch.channelId ORDER BY rating DESC