Problem with query not summing linked fields

PurpleMonkey

Hello.

I'm having some trouble with a query i'm doing where i'm trying to find the summation of three fields, cost_of_course, cost_of_travel and expenses_cost. the first two are in the table courses and the later is in the table expenses. They are linked by the PK in courses which is course_attendance_id and the FK in expenses of the same name. Anyway the query is:


$sql = "select sum(expenses_cost), sum(cost_of_course),";
$sql.= " sum(cost_of_travel) from staff, courses, expenses";
$sql.= " where staff.staff_id = courses.staff_id";
$sql.= " and courses.course_attendance_id = expenses.course_attendance_id";
$sql.= "  and staff.faculty = \"".$faculty."\"";

The problem is that the query will only make a sum of the cost_of_course and cost_of_travel if there is a least one expense for that course in the expense table, however, I want it to include in the sum table that to not have any expenses as well as those that do. Any thoughts?

goldbug

http://dev.mysql.com/doc/mysql/en/join.html

Have a look at MySQL JOIN syntax. Specifically the part on LEFT/RIGHT OUTER JOINs

PurpleMonkey

Ok... Thats not very clear that link, which is it i need to use, left or right join?

goldbug

Depends on which side of the join clause you put each table name.

Usually, if you want to join two tables, using every value of one table, and optional matches in a second table, you'd use a left join

A generalized version of the syntax:

SELECT a.*, b.*
FROM table1 a LEFT OUTER JOIN  table2 b ON a.matchcolumn = b.matchcolumn 
WHERE ...

In this case, you'll want to return all rows from the LEFT table (table1), and if there are any rows in the right table (table2) that satisfy the join condition (a.matchcolumn = b.matchcolumn), then return those as well.

PurpleMonkey

Ok, I've got this SQL now, but it won't work, any ideas?

select sum(c.cost_of_course), sum(c.cost_of_travel) from courses c left
outer join expenses e on c.course_attendance_id = e.course_attendance_id, staff s where
s.faculty="S&M"

goldbug

Without looking up the join precedence order (which means I'm probably wrong), I'll guess it's getting confused between your explicit left outer join and the implicit inner join (comma)

Maybe something like:

$sql = "SELECT SUM(c.cost_of_course), SUM(c.cost_of_travel) " .
       "FROM (courses c INNER JOIN staff s ON c.staff_id = s.staff_id) " .
       "LEFT OUTER JOIN expenses e ON c.course_attendance_id = e.course_attendance_id " .
       "WHERE s.faculty='" . $faculty . "'";