Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

My SQL is rusty, need help with a query

 
Bert Bates
author
Sheriff
Posts: 8905
5
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 8905
5
  • Likes 1
  • Mark post as helpful
  • send pies
  • 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 )
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic