homepage Welcome to WebmasterWorld Guest from 54.226.93.128
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Complex MySQL query
m8fyu




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

...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

 

VuoriLiikaluoma




msg:4246616
 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...

m8fyu




msg:4248651
 6:26 pm on Jan 3, 2011 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved