mysql join with conditional
- by Conor H
Hi There,
I am currently working on a MySQL query that contains a table:
TBL:lesson_fee
-fee_type_id (PRI)
-lesson_type_id (PRI)
-lesson_fee_amount
this table contains the fees for a particular 'lesson type' and there are different 'fee names' (fee_type). Which means that there can be many entries in this table for one 'lesson type'
In my query I am joining this table onto the rest of the query via the 'lesson_type' table using:
lesson_fee
INNER JOIN (other joins here)
ON lesson_fee.lesson_type_id = lesson_type.lesson_type_id
The problem with this is that it is currently returning duplicate data in the result. 1 row for every duplicate entry in the 'lesson fee' table.
I am also joining the 'fee type' table using this 'fee_type_id'
Is there a way of telling MySQL to say "Join the lesson_fee table rows that have lesson_fee.lesson_type_id and fee_type_id = client.fee_type_id".
UPDATE: Query:
SELECT
lesson_booking.lesson_booking_id,lesson_fee.lesson_fee_amount
FROM
fee_type INNER JOIN
(lesson_fee INNER JOIN
(color_code INNER JOIN
(employee INNER JOIN
(horse_owned INNER JOIN
(lesson_type INNER JOIN
(timetable INNER JOIN
(lesson_booking INNER JOIN CLIENT
ON
client.client_id = lesson_booking.client_id)
ON
lesson_booking.timetable_id = timetable.timetable_id)
ON
lesson_type.lesson_type_id = timetable.lesson_type_id)
ON
horse_owned.horse_owned_id = lesson_booking.horse_owned_id)
ON
employee.employee_id = timetable.employee_id)
ON
employee.color_code_id = color_code.color_code_id)
ON
lesson_fee.lesson_type_id = lesson_type.lesson_type_id)
ON
lesson_fee.fee_type_id = client.fee_type_id
WHERE booking_date = '2010-04-06'
ORDER BY lesson_booking_id ASC
How do I keep the format(indentation) of my query?
