MySQL query returns duplicate rows (JOIN error)

techguy

Hi There,

I'm trying to write a MySQL query the price for a lesson booking the DB based on the price category of the client. I have a table called 'lesson_fee' which stores the fees for a particular 'lesson_type'.

The problem i'm having with the following query is that it will return a row in the result for each entry in the 'lesson_fee' table that is associated with the 'lesson_type' I want it to return on the fee that is applicable i.e. set by 'fee_type_id' in the 'client' table.

It's seems that what I have just needs to have some kind of statement in the 'lesson_fee' join to select only where the 'fee_type_id' equals that of the 'client.fee_type_id'

Here's where i'm at so far:

SELECT
lesson_fee.lesson_fee_amount,
lesson_type.lesson_type_id,
lesson_fee.fee_type_id
FROM
fee_type INNER JOIN
		(lesson_fee 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
		lesson_fee.lesson_type_id = lesson_type.lesson_type_id)
ON
lesson_fee.fee_type_id = client.fee_type_id

Note: I have attached the shema for the tables involved if it is needed. You must rename the file to html as html files can't be uploaded.

Any thoughts on how I can fix this query are greatly appreciated.

Cheers!

Webmonkey

I don't understand what you want at all and that seems like a joint query for something that seems simple?

What is the exact output you looking for?

mark renton

You will need a lesson_type_id column in your lesson_booking table

at present there is no relationship between booking a lesson and the lesson type - you will not be able to create a query based on lesson type and obtain booking data

1 booking can have many lesson types (i'd imagine)

btw a correctly designed schema should make the sql query very simple

Beano

I have reformatted query your because it was impossible to read. Those brackets dont help at all and i cant see what they offer.

If your tables are normalised the way i think they appear to be then your joins dont seem correct. You have a join from the Client table to both lesson_fee and lesson_booking which does not seem right. the fee_type_id field on Client just doesnt seem right. Of course i have been up since 4am so i could be totally wrong.

I THINK your query should be

SELECT
lesson_fee.lesson_fee_amount,
lesson_type.lesson_type_id,
lesson_fee.fee_type_id
FROM
fee_type
INNER JOIN lesson_fee ON lesson_fee.fee_type_id = fee_type.fee_type_id
INNER JOIN lesson_type ON lesson_fee.lesson_type_id = lesson_type.lesson_type_id
INNER JOIN timetable ON lesson_type.lesson_type_id = timetable.lesson_type_id
INNER JOIN lesson_booking ON lesson_booking.timetable_id = timetable.timetable_id
INNER JOIN CLIENT ON client.client_id = lesson_booking.client_id

techguy

@john47832 The lesson booking table is linked to a 'timetable' table which stores the start_time, end_time, instructor, lesson_type etc for a given lesson. This table is linked to the 'lesson_type' table.

@webmonkey It's hard to explain but here's a quick attempt. I have a table that stores the fees (lesson_fee) associated with a lesson type. There can be many different fees for one lesson type. I want the query to return the booking info and the fee for the booking. the fee is determined by the fee_type_id attrib. of the client field. This fee_type_id attrib is also present in the lesson_fee table. Before I was getting a row in the result for each different fee in the lesson_fee table associated with a lesson_type. I fixed it by using

WHERE lesson_fee.fee_type_id = client.fee_type_id

@beano Thanks for that, I just wrote the query from scrath using your method. So much easier, would you believe I was thought to do it the way with the brackets in college. :eek: I havent done normalization but the DB seems to work pretty well for me. It did when there was just one fee stored in the lesson_type table. The reason for fee_type_id in client table is that it is used to determine what price band clients are to be charged, public private etc..

I've rewritten the query now to include the where statement at the end.

What do you guys think?

Here's my new query that works.

SELECT
CONCAT(client.client_firstname, ' ', client.client_lastname) AS client_name,
lesson_type.lesson_type_name,
lesson_fee.lesson_fee_amount
FROM
lesson_booking
INNER JOIN timetable ON lesson_booking.timetable_id = timetable.timetable_id
INNER JOIN lesson_type ON lesson_type.lesson_type_id = timetable.lesson_type_id
INNER JOIN CLIENT ON client.client_id = lesson_booking.client_id 
INNER JOIN lesson_fee ON lesson_fee.lesson_type_id = timetable.lesson_type_id WHERE lesson_fee.fee_type_id = client.fee_type_id
ORDER BY client.client_lastname ASC

Note: Rename attachment to .html

Beano

techguy wrote: »

@beano Thanks for that, I just wrote the query from scrath using your method. So much easier, would you believe I was thought to do it the way with the brackets in college. :eek: I havent done normalization but the DB seems to work pretty well for me. It did when there was just one fee stored in the lesson_type table. The reason for fee_type_id in client table is that it is used to determine what price band clients are to be charged, public private etc..

I've rewritten the query now to include the where statement at the end.

What do you guys think?

Here's my new query that works.
...

you seem to understand your own schema so the query makes sense given what you said