selecting rows with specific IDs for downstream analysis

Applications Programming
1

Hi,

I'm working hard on SQL and I came across a hurdle I'm hoping you can help me out with.

I have two tables

table1
headers: chrom start end name score strand

11   9720685 9720721 U0      0       +
21   9721043 9721079 U0      0       -
1   9721093 9721129 U0      0       +
20   9721485 9721521 U0      0       +
22   9721863 9721899 U0      0       -
21   9721863 9721899 U0      0       -
21   9721871 9721907 U0      0       -
21   9721872 9721908 U0      0       +
5   9722423 9722459 U0      0       +
4   9722434 9722470 U0      0       +

and table 2
headers: chrom start end name score strand

21   9719765 9749365 DOM1158 61.7441 +
21   10056465        10211865        DOM1159 321.867 +
1   31678598        31859898        DOM1160 73.8664 +
21   32133698        32300198        DOM1161 152.658 +
3   32599898        32910698        DOM1162 435.853 +
21   33177098        33447198        DOM1163 326.637 +
2   33669198        33728398        DOM1164 46.8648 +
21   33824598        34013298        DOM1165 116.821 +
20   34120598        34246398        DOM1166 197.878 +
20   34412898        34505398        DOM1167 98.2537 +

both tables contain 100+ entries and column one has numbers from 1-22.

my objective is to try to run this command

>select count(*) from table1, table2 where table1.start >= table2.start and table1.end <= table2.end;

but I want to get the information for rows matching chrom=21 only.

I've tried group by and then having, but it doesn't seem to work.

Can someone help me out? Thanks.

2

Not really clear what you're trying to select, post the desired output.

Regards

3

r u expecting this ?

select 
          count(*) from table1 a, table2 b 
where 
          a.start >= b.start 
          and a.end <= b.end
          and a.chrom = b.chrom
          and a.chrom = '21' ;