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.