# Problem with SQl statement/query

**URL:** <https://www.ruby-forum.com/t/problem-with-sql-statement-query/127223>\
**Category:** Rails\
**Created:** [January 26, 2008, 3:10pm UTC](https://www.ruby-forum.com/t/problem-with-sql-statement-query/127223 "2008-01-26T15:10:31Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![MarcHoeppner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f19dbf/32.png) [@MarcHoeppner](https://www.ruby-forum.com/u/MarcHoeppner)\
**Post date:** [January 26, 2008, 3:10pm UTC](https://www.ruby-forum.com/t/problem-with-sql-statement-query/127223/1 "2008-01-26T15:10:31Z")

</div>

Hi all,

I have some difficulties putting together a query using four tables,  
maybe someone can help me out on how to write the code:

table: organisms (id, organism)  
1 Organism1  
2 Organism2  
3 Organism3  
and so on…

table:genes (id, name, organism\_id, location, other columns)  
1 Gene1 1 chr1  
2 Gene2 1 chr1  
3 Gene3 1 chr1  
4 Gene 4 2 chr2

table:intexos(id, gene\_id,start,finish,sequence)  
1 1 23 45 sequence  
2 1 46 70 sequence  
3 1 71 98 sequence  
4 2 101 123 sequence  
5 2 124 156 sequence  
6 2 157 200 sequence

table: rnas (id,name,organism\_id, location, start, finish, sequence)  
1 rna1 1 chr1 26 42 sequence  
2 rna2 1 chr1 127 153 sequence

On normal language, here are the relations:

Each organism (table organisms) has a number of genes (table genes).  
Each gene can be broken down into introns and exons (table intexos,  
coding and non coding parts). Organisms also carry rna genes (table  
rnas), for which the same coordinate system is used. I now need to  
check which rna genes overlap with intexos (wich is a child of genes).  
The structure is not really up for discussion at this point for  
various reasons that have to do with performance and other  
considerations.

So, what I need the query to do is the following:

Find all rnas whose start/finish fall WITHIN the start/finish of an  
intexos from the same organism from the same location (in this case a  
chromosome).  
I can in principle write the sql query but I cant get it to limit the  
search to a particular organism\_id. In the application the user would  
get a list of organisms, and then perform the query discussed above  
for one particular organism only. I thought I could do it by going via

@organism = Organism.find(params[:id])  
@rnas = @organism.rnas.find\_by\_sql(“a query here”)

But that gives me a list of ALL elements from the rnas table that fall  
within the boundaries of an element from intexos rather then only  
those elements from a particular organism. Drives me nuts 😃

Any suggestions?

Cheers,

Marc