3 table query, over my head

• • Reply

  13 years ago

  by climber59

  al

  , United States

  Joined 13 years ago
  I have done a lot of moderate to simple queries, but this one is more complex & I can't seem to wrap my head around it...
  
  I have the following 3 tables:
  
  states
   _ID
   name
   abbrev
   region
  
  regions
   _ID
   name
  
  resort_info
   _ID
   name
   address
   city
   state
   zip
  
  the region field in the states table contains the primary key (_ID) of the regions table. the state field in the users table contains the abbreviation for the state, it's the same as the abbrev field in the states table.
  
  the problem is if I have a given region _ID, how can I get the list of users (and all fields) in that region?
   
  this query works, but displays the correct 3 resorts 8 times, the same number as the number of regions.
  
  SELECT *
  FROM resort_info, states, regions
  WHERE states.region = 5
  AND states.abbrev = resort_info.state_province;
  
  any help would be greatly appreciated.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse