• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

My SQL is rusty, need help with a query

 
author
Posts: 9050
21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Let's say we have a travel application that allows travelers to find people to do stuff with in various cities. So, if I'm traveling to Denver I can ask:

Who lives in Denver that plays tennis?

== My table design is really simple, three tables, two join tables:

Cities table has two columns: c-id, city-name
Activities table has two columns : a-id, activity
Person table has three columns : p-id, person-name, city-id

the join tables:

city-activity has two columns: c-id, a-id
person-activity has two columns : p-id, a-id

== two questions

1 - Is this a reasonable design?
2 - Assuming we know that Denver is c017 and tennis is a012, how would we write the query:

Who lives in Denver that plays tennis?

Thanks!
 
Bert Bates
author
Posts: 9050
21
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Our query in English: "If Denver has public tennis courts, give me a list of people in Denver who play tennis."

Andrew Monkhouse's fine SQL version (which we've agreed uses five, well-designed tables):



Thanks to Andrew, and notice how complicated it is just to support two, many-to-many relationships. (I'm learning Neo4j )
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic