# Find By SQL + multiple tables

**URL:** <https://www.ruby-forum.com/t/find-by-sql-multiple-tables/77801>\
**Category:** Rails\
**Created:** [March 7, 2006, 1:28pm UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-tables/77801 "2006-03-07T13:28:47Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jjantz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@jjantz](https://www.ruby-forum.com/u/jjantz)\
**Post date:** [March 7, 2006, 1:28pm UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-tables/77801/1 "2006-03-07T13:28:47Z")

</div>

Hello all. I am trying to get the following SQL to work in Ruby on rails  
and Oracle.

SELECT bugsheet.\*, bugcomplete.bc\_time FROM bugsheet, bugcomplete WHERE  
bugsheet.bs\_id = bugcomplete.bc\_id AND bugsheet.bs\_patchrel like  
‘5.1.12-B4%’ ORDER BY bs\_cdate DESC, bc\_time DESC

If I type that into an SQL program it works fine, if I try and use the  
following in RoR:

Bugsheet.find\_by\_sql[“SELECT bugsheet.\*, bugcomplete.bc\_time FROM  
bugsheet, bugcomplete WHERE bugsheet.bs\_id = bugcomplete.bc\_id AND  
bugsheet.bs\_patchrel  
like ? ORDER BY ?”, @criteria, sort\_clause]

It returns an empty array. I have checked that @criteria and sort\_clause  
are populated using the breakpointer.

Is it because I am selecting from two tables using a single models  
.find\_by\_sql?

I thought about using left join:

SELECT bugsheet.bs\_cdate, bugcomplete.bc\_time FROM bugsheet left join  
bugcomplete ON bugsheet.bs\_id = bugcomplete.bc\_id ;

But that gives me an error in the SQL program, let alone RoR

Any help from RoR / database gurus would be much apprciated and rewarded  
with a metaphorical cookie 🙂

Jeff

---

<div class="post-metadata">

**Author:** ![jjantz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@jjantz](https://www.ruby-forum.com/u/jjantz)\
**Post date:** [March 7, 2006, 2:00pm UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-tables/77801/2 "2006-03-07T14:00:08Z")

</div>

Jeff J. wrote:

> Bugsheet.find\_by\_sql[“SELECT bugsheet.\*, bugcomplete.bc\_time FROM  
> bugsheet, bugcomplete WHERE bugsheet.bs\_id = bugcomplete.bc\_id AND  
> bugsheet.bs\_patchrel  
> like ? ORDER BY ?”, @criteria, sort\_clause]
> 
> It returns an empty array. I have checked that @criteria and sort\_clause  
> are populated using the breakpointer.

Confirm by looking at the DB queries in your Rails log, but I think  
your problem is that your ORDER BY clause is getting quoted. Write  
instead “…ORDER BY #{sort\_clause}”.

–  
We develop, watch us RoR, in numbers too big to ignore.