ansaurus

Question

Answer 1

A:

It's almost what you write in english

"Join the lesson_fee table rows that have lesson_fee.lesson_type_id and fee_type_id = client.fee_type_id".

FROM lesson_fee 
INNER JOIN client ON 
  lesson_fee.lesson_type_id=client_lesson_type_id AND  
  lesson_fee.fee_type_id=client.fee_type_id

Assuming lesson_fee (lesson_type_id,fee_type_id) is unique, then this will return one row from the lesson_fee table,rather than one for each fee type.

mdma 2010-05-28 23:41:33

Answer 2

+1 A:

Please learn to use brackets in SQL correctly - I've re-written your existing query:

SELECT lb.lesson_booking_id,
       lf.lesson_fee_amount 
   FROM FEE_TYPE ft
   JOIN TIMETABLE tt  --cross join
   JOIN COLOR_CODE cc --cross join 
   JOIN EMPLOYEE e ON e.employee_id = tt.employee_id
                  AND e.color_code_id = cc.color_code_id
    JOIN HORSE_OWNED ho ON ho.horse_owned_id = lb.horse_owned_id
    JOIN LESSON_TYPE lt ON lt.lesson_type_id = tt.lesson_type_id
    JOIN LESSON_BOOKING lb ON lb.timetable_id = tt.timetable_id
    JOIN CLIENT c ON c.client_id = lb.client_id
    JOIN LESSON_FEE lf ON lf.lesson_type_id = lt.lesson_type_id
                      AND lf.fee_type_id = c.fee_type_id
   WHERE booking_date = '2010-04-06' 
ORDER BY lesson_booking_id ASC

A cross join is a cartesian product. If you don't specify criteria (IE: ON ....) on an INNER JOIN in MySQL - the result is a cross join/cartesian product.

ONLY USE brackets when one or more things need to be performed together. Example:

WHERE a = b AND c = d OR c = e

...will return a different result set from:

WHERE (a = b AND c = d) OR c = e

You would only use brackets AFTER the ON portion of the clause - there is no nesting. The nesting comes from the join criteria itself. I try to structure my queries so that it reads top down, so that based on the join criteria you can see how one table relates to the next. IE if you have a table relating to two or more tables, it should be higher on the list than the others, because the others are dependent on it. Look at TIMETABLE as an example...

OMG Ponies 2010-05-28 23:50:14

ansaurus

tags:

views:

answers:

mysql join with conditional

related questions