# can not fetch data from other table

**URL:** <https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879>\
**Category:** rubyonrails-talk\
**Created:** [September 2, 2008, 6:06am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879 "2008-09-02T06:06:04Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 6:06am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/1 "2008-09-02T06:06:04Z")

</div>

hello , problem while i am trying this query

@location=Location.find\_by\_sql("SELECT DISTINCT country FROM locations WHERE state = '#{@site\_details.location}'")

in @site\_details.location i am having city name Newyork base on this i want related states and country on view file . i am not able to fetch this data .

Thing id that when i found state from input i want city and country to display based on state !

please help !

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 6:15am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/2 "2008-09-02T06:15:40Z")

</div>

Can u be some clear… i hope ur trying to get the result like

@[site\_details.location.state.country.name](http://site_details.location.state.country.name)

if location is a table rather than a entity in ur site\_details table and in ur site\_details table make location\_id as an entity.

🙂Bala

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 7:27am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/3 "2008-09-02T07:27:38Z")

</div>

I consider the fallowing models that u had

class SiteDetails \< ActiveRecord::Base belongs\_to :state

class State \< ActiveRecord::Base has\_many :site\_details belongs\_to :city

class City \< ActiveRecord::Base has\_many :states belongs\_to :country

class country \< ActiveRecord::Base has\_many :cities

in ur controller

def show @site\_details = SiteDetail.find(params[:id])

end

in ur view show.html.erb

\<%= @[site\_details.state.name](http://site_details.state.name) %\> Gives u the state name \<%= @[site\_details.state.city.name](http://site_details.state.city.name) %\> gives u the city name of the site details

\<%= @[site\_details.state.city.country.name](http://site_details.state.city.country.name) %\> gives u the country name

cities table(id,name,country\_id) states table(id,name,city\_id) countries table(id,name)

site\_details table(id,state\_id,ur\_field1,ur\_field2…)

Hope ur trying to do the same.🙂Bala

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 9:12am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/4 "2008-09-02T09:12:11Z")

</div>

thx

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 9:13am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/5 "2008-09-02T09:13:17Z")

</div>

Dharmdip Rathod wrote:

> thx

but i have all fields in same table called locations .. there are fields name id country city state

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 9:18am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/6 "2008-09-02T09:18:21Z")

</div>

:o

ok then i hope in ur site\_details u had stored location\_id even …

class Location \< ActionRecord :: Base has\_many :site\_details

class SiteDetail \< ActiveRecord :: Base belongs\_to :location

if so u can get directly like

@site\_details.locations.city@site\_details.locations.state@site\_details.locations.country

🙂Bala

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 9:30am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/7 "2008-09-02T09:30:22Z")

</div>

undefined method `country' for "Newyork":String i am getting this error !

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 9:52am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/8 "2008-09-02T09:52:00Z")

</div>

it seems that ur site\_details.locations holds “name” instead of the table

c the bottom line is

if ur table is like locations(id,city,country,name) and site\_details like(id,location\_id…)

then the above code works

but it seems that ur site\_details.location itself contains “country\_name”…

so therz no relation b/w locations and site\_details.

if u still want to use the same u can do like

@location = Location.find(:first,:conditions =\> [‘country=?’,@site\_details.locations])

now u can do @location.city@location.state

🙂

Bala

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 10:02am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/9 "2008-09-02T10:02:28Z")

</div>

You have a nil object when you didn't expect it! The error occurred while evaluating nil.location

i am getting this ! i know there is some silly mistake but i am trying to solve

!

;(

by the way thanks for u r support !

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 11:11am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/10 "2008-09-02T11:11:27Z")

</div>

what ur gettig in the @site\_details array

use puts @site\_details.inspect in ur controller and check the result wether ur getting the @site\_details.location or not

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 11:19am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/11 "2008-09-02T11:19:24Z")

</div>

i am trying ur suggestion after some time but while i am trying this query

\<p\>URL : \<%= @site\_detail.url%\>\</p\> \<p\>TITLE: \<%= @site\_detail.title%\>\</p\> \<p\>Email: \<%= @site\_detail.email%\>\</p\> \<p\>Posting ID: \<%= @site\_detail.postingid%\>\</p\> \<p\>Date: \<%= @site\_detail.date%\>\</p\> \<p\>Price: $\<%= @site\_detail.price%\>\</p\> \<p\>City: \<%= @site\_detail.location%\>\</p\> \<p\>Country: \<%=Location.find\_by\_sql("SELECT DISTINCT country, state FROM locations WHERE city = '#{@site\_detail.location}'").to\_s%\>\</p\>

TITLE: Jersey City Indoor Parking

Email: hous-815440723@craigslist.org

Posting ID: 815440723

Price: $200.0

City: Newyork

Country: ###############

i am getting detail like this in country !

meaning of ############## is total records in db. i have tried all string convert type but not successful... thx i am trying ur solution !

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 11:27am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/12 "2008-09-02T11:27:56Z")

</div>

Is city name in the locations table is unique???

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 11:31am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/13 "2008-09-02T11:31:30Z")

</div>

and ur selecting both the country and state at a time which results an array or a hash. so u can do like

\<% location = Location.find(:first,:conditions =\> [‘city ?’,@site\_detail.location])%\>

\<% for loc in location %\> Country \<%= loc.country %\> State:\<%= loc.state %\>

\<%end -%\>

if city is the unique in locations table…

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 11:32am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/14 "2008-09-02T11:32:20Z")

</div>

Bala kishore Pulicherla wrote:

> Is city name in the locations table is unique???
> 
> On Tue, Sep 2, 2008 at 4:49 PM, Dharmdip Rathod \<

NO, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;id country city state

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 usa alabama auburn &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 usa alabama birmingham &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 usa alabama columbus,GA &nbsp;&nbsp;&nbsp;&nbsp;4 usa alabama dothan &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 usa alabama florence / muscle shoals &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6 usa alabama gadsden-anniston &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7 usa alabama huntsville &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8 usa alabama mobile &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9 usa alabama montgomery &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10 usa alabama tuscaloosa

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 11:34am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/15 "2008-09-02T11:34:26Z")

</div>

sorry data is not formatted !

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 11:35am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/16 "2008-09-02T11:35:23Z")

</div>

:o

even though u say distinct(state),distinct(city) u’ll get the array only…

☹

make sure u maintain a relation b/w the site\_detail and locations other wise ☹

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 11:40am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/17 "2008-09-02T11:40:20Z")

</div>

Bala kishore Pulicherla wrote:

> :o
> 
> even though u say distinct(state),distinct(city) u'll get the array only...
> 
> ☹
> 
> make sure u maintain a relation b/w the site\_detail and locations other wise☹
> 
> On Tue, Sep 2, 2008 at 5:02 PM, Dharmdip Rathod \<

this is simple thing even i know logic but cant convert it ! i am so helpless ! but u give me lots of support when i fire query in mysql i got result but here not ! what to do i dont know ! but ................... deadline is deadline no excuse ! :)- thanks ! for ur support

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 11:42am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/18 "2008-09-02T11:42:31Z")

</div>

Do u want to display all the states and countries for that city…???

or u want to display the country and state of the particular site??

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [September 2, 2008, 11:49am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/19 "2008-09-02T11:49:41Z")

</div>

Bala kishore Pulicherla wrote:

> Do u want to display all the states and countries for that city..???
> 
> or u want to display the country and state of the particular site??
> 
> On Tue, Sep 2, 2008 at 5:10 PM, Dharmdip Rathod \<

display the country and state of the particular city??

---

<div class="post-metadata">

**Author:** ![bala\_kishore\_puliche](https://avatars.discourse-cdn.com/v4/letter/b/da6949/32.png) [@bala\_kishore\_puliche](https://discuss.rubyonrails.org/u/bala_kishore_puliche)\
**Post date:** [September 2, 2008, 11:54am UTC](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879/20 "2008-09-02T11:54:43Z")

</div>

as per ur db design u can do up to

URL : \<%= @site\_detail.url%\>

TITLE: \<%= @site\_detail.title%\>

Email: \<%= @site\_detail.email%\>

Posting ID: \<%= @site\_detail.postingid%\>

Date: \<%= @site\_detail.date%\>

Price: $\<%= @site\_detail.price%\>

City: $\<%= @site\_detail.location%\>

\<% countries = Location.find(:all,:select =\> ['country'], :conditions =\> ['city =?',@site\_detail.location])

\<% for country in countries %\> Country Name :\<%= country %\> \<%end %\> \<% states = Location.find(:all,:select =\> [‘state’], :conditions =\> [‘city =?’,@site\_detail.location]) %\>

\<%for state in states %\> State :\<%= state %\> \<%end %\>

🙂 as ur city name is for many states and countries.

if u want the other way consult ur db designer who designed the DB :x

🙂Bala

[Next page](https://discuss.rubyonrails.org/t/can-not-fetch-data-from-other-table/33879.md?page=2)