locked
Many to many Relationship RRS feed

  • Question

  • User-2075123566 posted

    hi,

    I working on website and i have ready database from the company database. 

     

    I have a table Location has only two fields : city and state.

     

    I tried to get select the * city where state = something , but i think it is wrong,


    Is it a Many-to-Many realtionship ? do you have an idea ?

    Sunday, April 18, 2010 7:09 AM

Answers

  • User1096912014 posted

    You may have another table called States (State Char(2) primary key, FullName varchar(200)). Then Location table and States tables will be related as One to Many

    One State -> Many cities.

    To get cities for the particular state you'll use

    declare @State char(2)

    set @State = 'GA'

    select L.State, L.City, S.FullName from Location L inner join States S on L.State = S.State where L.State = @State

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 18, 2010 5:47 PM
  • User364663285 posted

     

    hi,

    I working on website and i have ready database from the company database. 

     

    I have a table Location has only two fields : city and state.

     

    I tried to get select the * city where state = something , but i think it is wrong,


    Is it a Many-to-Many realtionship ? do you have an idea ?

    1. state is one bigger group, which has many cities inside. It's only relation and it does not involve any many to many relationships
    2.  you can put one unique constraint for the state
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 18, 2010 11:47 PM
  • User1789523204 posted

    First Study the requirments
    Which are given to you
    many to many or any other relation depend upon the requirments
    If your requirments is not correct you will not be able to sketch a good database
    So

    Study the requirments and then sketch the relationship

    Location and States may be the two tables

    States containt id , code ,name , countryId

    Location Containt
    LocationID , StateId , LocationName , City

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 19, 2010 1:10 AM

All replies

  • User1759999623 posted

    What is your exact SQL SELECT statement?

    Sunday, April 18, 2010 9:00 AM
  • User1096912014 posted

    You may have another table called States (State Char(2) primary key, FullName varchar(200)). Then Location table and States tables will be related as One to Many

    One State -> Many cities.

    To get cities for the particular state you'll use

    declare @State char(2)

    set @State = 'GA'

    select L.State, L.City, S.FullName from Location L inner join States S on L.State = S.State where L.State = @State

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 18, 2010 5:47 PM
  • User364663285 posted

     

    hi,

    I working on website and i have ready database from the company database. 

     

    I have a table Location has only two fields : city and state.

     

    I tried to get select the * city where state = something , but i think it is wrong,


    Is it a Many-to-Many realtionship ? do you have an idea ?

    1. state is one bigger group, which has many cities inside. It's only relation and it does not involve any many to many relationships
    2.  you can put one unique constraint for the state
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 18, 2010 11:47 PM
  • User1789523204 posted

    First Study the requirments
    Which are given to you
    many to many or any other relation depend upon the requirments
    If your requirments is not correct you will not be able to sketch a good database
    So

    Study the requirments and then sketch the relationship

    Location and States may be the two tables

    States containt id , code ,name , countryId

    Location Containt
    LocationID , StateId , LocationName , City

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 19, 2010 1:10 AM