CODEWITHSUNDEEP
phpmysqldatabasejoin
gordyr
gordyr

Reputation: 6276

How can I add second join which calculates an AVG to this query?

I have a query which joins two tables and counts the total in a second table by song ID.

How can I modify this query to include an average of the column 'ratings' in a third table ($sTable3) again, with the same song id.

    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."

    FROM $sTable b 
    LEFT JOIN (
   SELECT COUNT(*) AS projects_count, a.songs_id

   FROM $sTable2 a
   GROUP BY a.songs_id
) bb ON bb.songs_id = b.songsID


        $sWhere
        $sOrder
        $sLimit
    ";

This is all put into a JSON array and I would like to return this 'average' under a new column 'ratings'.

To summarize (as i'm aware I may not be articulating this well):-

I have three tables $sTable, $sTable2, $sTable3. All three share a songID column. My current query joins the first two and returns all results exactly as I want. However I need to also retrieve data related to the songID from the 'rating' column of $sTable3.

Upvotes: 0

Views: 54

Answers (1)

Cylindric
Cylindric

Reputation: 5894

Had to guess a lot of your problem, as you don't reveal much about the setup, just some (inexplicably cryptically-named) variables.

SELECT songs.title, AVG(ratings.rating), COUNT(something.songs_id)
FROM songs
LEFT JOIN something ON (songs.songs_id=something.songsID)
LEFT JOIN ratings ON (songs.songs_id=ratings.songsID)
GROUP BY songs.title

Upvotes: 1

Related Questions