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.