# RE: best way to combine results from two tables

**URL:** <https://www.ruby-forum.com/t/re-best-way-to-combine-results-from-two-tables/79038>\
**Category:** Rails\
**Created:** [March 9, 2006, 5:06pm UTC](https://www.ruby-forum.com/t/re-best-way-to-combine-results-from-two-tables/79038 "2006-03-09T17:06:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![stickstone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6f9a4e/32.png) [@stickstone](https://www.ruby-forum.com/u/stickstone)\
**Post date:** [March 9, 2006, 5:06pm UTC](https://www.ruby-forum.com/t/re-best-way-to-combine-results-from-two-tables/79038/1 "2006-03-09T17:06:36Z")

</div>

I’m going to throw my .02 in on this one.

I’m a long time DBA, and in my opinion, some things are just better left  
to the database as a view (as you stated), a stored procedure, or a  
table returning function. There are many many complex data operations  
that will occur much faster if run natively on the database.  
(Operations that require temp tables, and cubes, etc. come to mind)

At any rate, my guess is that rails would treat a view and a tables the  
same way…

* * *

From: removed\_email\_address@domain.invalid  
[[mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)] On Behalf Of Larry W.  
Sent: Wednesday, March 08, 2006 5:04 PM  
To: removed\_email\_address@domain.invalid  
Subject: Re: [Rails] best way to combine results from two tables

I’ve got the farmer table down 🙂

I’m going to try an implementation that selects from both tables and  
merges the results in the controller. In my application, I think I can  
do this safely because there’s a limited amount of current data (per  
farmer). Also going to try to create a pagination object manually on  
the merged data. I’ll let you know how it turns out.

The only alternative i could think of is to merge all the data in a  
single table as you suggested, possibly using updatable views to create  
models for each of the subtypes, but that seems to add a lot of  
overhead.

On 3/8/06, Craig W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

OK - in that case, I am definitely interested if someone takes the time  
to explain because I am working on reports that need to iterate over a  
lot of items in a different manner but similarly enough that I might  
learn something useful.

Craig

ps…I still would have a ‘farmers’ table

On Wed, 2006-03-08 at 16:19 -0500, Larry W. wrote:

> That might have been best, but the animals are very different. lots of

---

<div class="post-metadata">

**Author:** ![stickstone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6f9a4e/32.png) [@stickstone](https://www.ruby-forum.com/u/stickstone)\
**Post date:** [March 9, 2006, 5:09pm UTC](https://www.ruby-forum.com/t/re-best-way-to-combine-results-from-two-tables/79038/2 "2006-03-09T17:09:41Z")

</div>

I was wondering about that (view-table equivalence). Also not sure how  
to  
create a view that concatenates data from two tables (rather than  
joining),  
but I posted on the postgresql list for that one.

Since I’m starting to think that rolling my own would mean i can’t get  
pagination to work, i will probably try the db way.

---

<div class="post-metadata">

**Author:** ![stickstone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6f9a4e/32.png) [@stickstone](https://www.ruby-forum.com/u/stickstone)\
**Post date:** [March 9, 2006, 5:18pm UTC](https://www.ruby-forum.com/t/re-best-way-to-combine-results-from-two-tables/79038/3 "2006-03-09T17:18:49Z")

</div>

You probably want UNION:

create view farm\_view as  
select ‘cow’ as animal\_type, name, age, farmer from cows  
union  
selec ‘sheep’ as animal\_type, name, age, farmer from sheeps  
union  
selec ‘horse’ as animal\_type, name, horse\_age as age, farmer from  
horses  
order by name, age, farmer desc ;

---

<div class="post-metadata">

**Author:** ![stickstone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6f9a4e/32.png) [@stickstone](https://www.ruby-forum.com/u/stickstone)\
**Post date:** [March 9, 2006, 5:18pm UTC](https://www.ruby-forum.com/t/re-best-way-to-combine-results-from-two-tables/79038/4 "2006-03-09T17:18:54Z")

</div>

I did it with a union. now i’m going to try to build a model and see  
how  
rails deals with it.