Community discussion forum

how to do this

• • Reply

  2 years ago

  by chezhian_in05

  chezhian_in05

  chennai, India

  Joined 4 years ago

  hi developers

      i've a table named users where i store the city id ,

  this city id -> citymaster (cityid,stateid)->state master (cityid,stateid) ->country master (countryid,stateid).

  now i need to make the query fetches the columns like this

  username,cityid,stateid,countryid

  how can i do this

  Report abuse
• • Reply

  2 years ago

  by sankar_mdu2

  sankar_mdu2

  , India

  Joined 5 years ago
  try this qry..
  select username,cityid,stateid,countryid
      from Users u
      left join city ct on (u.cityid=ct.cityid)
      left join state s on (ct.stateid=s.stateid)
      left join country cn (s.countryid=cn.countryid)
  
  
  
  
  
  
  
  Report abuse
• • Reply

  2 years ago

  by mmharinath

  M M Harinath

  Chennai, India

  Joined 3 years ago

  Hi,

  you can not use column name cityid,stateid directly in the query. because you have these column in more than one table. if you execute this query, u will get "ambiguous column name" error

   

  Try with the following query

  select username,u.cityid,s.stateid,cn.countryid
      from Users u
      left join city ct on (u.cityid=ct.cityid)
      left join state s on (ct.stateid=s.stateid)
      left join country cn (s.countryid=cn.countryid)
  
  
  
  

  Report abuse