locked
Inner join on table RRS feed

  • Question

  • Hi,

      I have 4 tables:

    tblcountry has "Countryid"-P.K and "Country"

    tblstate has "Countryid"-F.K, "Stateid"-P.K and "State

    tblcity has "Cityid"-P.K,"Stateid"-F.K and "City"

    tbluserlist has "name", country, state, city

    The country,state and city in tbluserlist are inserted from above three tables from a dropdownbox..

    It displays countryid instead of country.,e.g ("SELECT country FROM tbluserlist" results in displaying countryid) ..

    I tried with the inner join.

    select country from userlist inner join tblcountry on userlist.country=tblcountry.countryid

    I'm getting error as "Ambigous column country"

    I try modifying tbluserlist column name "country" to "countryname" then it shows countryid as result ..Plz Help

     

    • Moved by Vicky SongMicrosoft employee Thursday, September 6, 2012 1:51 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Wednesday, September 5, 2012 1:05 PM

Answers

  • Because of having Two country column in two table you want to join , SQL  don't know to use which column to built a table. (Don't remember result of join tables is a table.)

    Addition of this, A table couldn't have two same name column.

    If you use  .   between column name and  table name this problem will be solve. like below:

    select c.country from userlist u join tblcountry c on u.country = c.countryid
    • Proposed as answer by SQL IT Sunday, September 9, 2012 6:52 AM
    • Marked as answer by Kalman Toth Tuesday, September 11, 2012 8:24 PM
    Thursday, September 6, 2012 6:39 AM

All replies

  • Hi Sakthivel,

    I am moving your issue to the T-SQL forum so that you can get better support there.

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, September 6, 2012 1:50 AM
  • Try the below query:

    SELECT	U.Name AS UserName
    ,		C.Country AS CountryName
    --,		S.State as StateName		-- uncomment this (along with JOIN) to get state name
    --,		C.City AS CityName			-- uncomment this (along with JOIN) to get city name
    FROM	tblUserList U
    JOIN	tblCountry C
    ON		U.Country = C.CountryID
    --JOIN	tblState S
    --ON		U.State = S.StateID		-- uncomment this JOIN if you need state name
    --JOIN	tblCity C
    --ON		U.City = C.CityID		-- uncomment this JOIN if you need city name
    Thanks!
    Thursday, September 6, 2012 3:53 AM
  • I'm getting error as "Ambigous column country"

    Sakthivel, you would get this error when you try to select a column which is present in more than one table in the Join. This would be resolved by adding the table alias to the column name. so the query

    select userlist.country from userlist inner join tblcountry on userlist.country=tblcountry.countryid

    Coming to the other point, the countryid is getting displayed since you are pulling the countryid from the userlist table. You have to pull the countryname table from tblcountry as Deepak has mentioned..


    Murali Krishnan

    Thursday, September 6, 2012 4:05 AM

  • select country from userlist inner join tblcountry on userlist.country=tblcountry.countryid

    I'm getting error as "Ambigous column country"


    Hi Sathvikel,

    U r getting this error because both tables "Userlist and Tbl Country has the same column name,So SQL engine cannot decide which column to display and hence u ll end yp with ambiguos Column Error.u can over come this problem by giving table alliases or tableName.colunmn Names.

    As shwn below

    select UserList.country from userlist inner join tblcountry on userlist.country=tblcountry.countryid


    Please have look on the comment

    Thursday, September 6, 2012 5:18 AM
  • >> I have 4 tables: <<

    How nice for you! Where is the DDL? Why did you prefix the tables with “tbl_”? Why don't you use ISO Standards? Good programmers use a website called Google today and look up standards before we code. 

    CREATE TABLE Countries --- ISO-3166 
    (country_code CHAR(3) NOT NULL PRIMARY KEY,
     country_name VARCHAR(30) NOT NULL);

    You then screwed up the next narrative. If you were not willing to research the UN codes for political sub-units, why should anyone else? But the PK is wrong; many states share the same names so you need a compound key. 

    CREATE TABLE States
    (country_code CHAR(3) NOT NULL 
     REFERENCES Country_codes,
     state_code CHAR(3) NOT NULL,
     PRIMARY KEY (country_code, state_code), 
     state_name VARCHAR(30) NOT NULL);

    CREATE TABLE Cities
    (country_code CHAR(3) NOT NULL 
     state_code CHAR(3) NOT NULL,
     FOREIGN KEY (country_code, state_code)
     REFERENCES States,
     city_code CHAR(3) NOT NULL,
     PRIMARY KEY (country_code, state_code, city_code),
     city_name CHAR(35) NOT NULL);

    A list is a data structure, like “tbl_”, and since it is meta data you never use it in a data element name. Notice how the keys are nested to give you a geography hierarchy. 

    CREATE TABLE Users 
    (user_name VARCHAR(255) NOT NULL PRIMARY KEY, --- email?
     country_code CHAR(3) NOT NULL, 
     state_code CHAR(3) NOT NULL,
     city_code CHAR(3) NOT NULL,
     FOREIGN KEY (country_code, state_code, city_code)
     REFERENCES Cities);

    >> The country, state and city in Users table are inserted from above three tables from a drop down box. <<

    There are no drop down boxes in SQL. That stuff is in a presentation or input layer in the tiered architecture. This is a database forum. You meant to post somewhere else. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 6, 2012 5:33 AM
  • Use below code (added highlighted code):

    select tblcountry.country from userlist inner join tblcountry on userlist.country=tblcountry.countryid


    Manoj Mandal

    Thursday, September 6, 2012 5:37 AM
  • Because of having Two country column in two table you want to join , SQL  don't know to use which column to built a table. (Don't remember result of join tables is a table.)

    Addition of this, A table couldn't have two same name column.

    If you use  .   between column name and  table name this problem will be solve. like below:

    select c.country from userlist u join tblcountry c on u.country = c.countryid
    • Proposed as answer by SQL IT Sunday, September 9, 2012 6:52 AM
    • Marked as answer by Kalman Toth Tuesday, September 11, 2012 8:24 PM
    Thursday, September 6, 2012 6:39 AM