mysql join with conditional
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
Update: Output:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>query data</title>
<style type="text/css" <!--
.normal { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; font-weight: normal; color: #000000}
.medium { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 15px; font-weight: bold; color: #000000; text-decoration: none}
--></style>
</head>
<body>
<h3>query result</h3><table border=1>
<tr>
<td bgcolor=silver class='medium'>lesson_booking_id</td><td bgcolor=silver class='medium'>lesson_fee_amount</td></tr>
<tr>
<td cl开发者_StackOverflowass='normal' valign='top'>0</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>0</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>0</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>0</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>5</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>5</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>5</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>5</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>9</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>9</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>9</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>9</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>11</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>11</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>11</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>11</td>
<td class='normal' valign='top'>25.00</td>
</tr>
<tr>
<td class='normal' valign='top'>13</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>13</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>13</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>13</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>16</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>16</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>16</td>
<td class='normal' valign='top'>500.00</td>
</tr>
<tr>
<td class='normal' valign='top'>16</td>
<td class='normal' valign='top'>500.00</td>
</tr>
</table>
</body></html>
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...
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.
加载中,请稍侯......
精彩评论