INSERT INTO (Select ...)

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

Is this possible to do a subquery with a Insert Into?

For example:

Code:




$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grade (Course_ID, SOMS_KEY, UID, Irat_Avg, Grat_Avg, Appex_Avg)


SELECT c.Course_ID, g.SOMS_KEY, g.UID, AVG(g.IRAT_Grade) AS Irat_Avg, AVG(g.GRAT_Grade) AS Grat_Avg, AVG(g.Appex_Grade) AS Appex_Avg


FROM ".$prefix."_tl_session_grades g


JOIN ".$prefix."_tl_session s ON (g.Session_ID = s.Session_ID)


JOIN ".$prefix."_tl_courses c ON (s.Course_ID = c.Course_ID)


WHERE c.Course_ID = '$Course_ID'");

registered · Posted: Mon Mar 17, 2008 6:14 am

Donovan, how did you make out on this?

Posted: Tue Mar 18, 2008 6:39 am

This seems to be working for me.

Code:

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Irat_Avg, Grat_Avg, Appex_Avg) SELECT DISTINCT s.Course_ID, g.SOMS_KEY, g.UID, g.Academic_Year, AVG(IRAT_Grade) AS Irat_Avg, AVG(GRAT_Grade) AS Grat_Avg, AVG(Appex_Grade) AS Appex_Avg


FROM ".$prefix."_tl_session_grades g


JOIN ".$prefix."_tl_session s ON (g.Session_ID = s.Session_ID)


JOIN ".$prefix."_tl_courses c ON (s.Course_ID = c.Course_ID)


WHERE c.Course_ID = '$Course_ID'


GROUP BY g.SOMS_KEY");


if (!$insertgrades) {


      echo("<p>Error performing query: " . mysql_error() . "</p>");


      exit();   


      }

Posted: Wed Mar 19, 2008 5:28 am

Most excellent sir.