Welcome to WebmasterWorld Guest from 54.167.185.18

Forum Moderators: open

Message Too Old, No Replies

Complex MySQL query

   
9:25 am on Dec 20, 2010 (gmt 0)

5+ Year Member



...well its complex for me anyway!

Merry Christmas guys.

I wonder if someone could offer me some advice please. I'm pretty new to MySQL and am finding the subject fascinating. I'm pretty much getting to grips with the syntax now and finding a few new things out every day. I love it but have been getting a little frustrated over the last couple of day as I can't seem to write a query that I need.

I'm working on a small project where I want to list boat trips in various locations round the world. Each boat had many trips associated with it and each trip has many products (tickets with various prices) associated with it

My aim is to list every boat (just the once) in any given location and display the lowest price of a ticket.

Eg: The Thames Barge starting from 14

My DB is set up like so:

locations
id
name

boats
id
name
fk_location_id

trips
id
name
fk_location_id
fk_boat_id

products
id
name
fk_service_id
selling_price

Can anyone please give me any pointers on how to write my query? Ii can figure it out in my head what I'm trying to achieve, but breaking it down correctly so that I can write the query is stumping me. I think that a little help would go a long way in helping the penny to drop.

Thanks
10:49 am on Dec 28, 2010 (gmt 0)



You should check out MySQL MIN() aggregate function and GROUP BY.

for ex: SELECT a, MIN(b) FROM c GROUP BY d;

Hope that helps somewhat...
6:26 pm on Jan 3, 2011 (gmt 0)

5+ Year Member



Sorry for the late reply. Thanks so much for the information. I'll be sure to read up and get back to let you know how I get on. Thanks again.