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       +

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 +

quote:

---

Originally posted by lasqlhelp

I see so I would simply type:

SELECT count(*) FROM table1, table2 WHERE table1.chrom IN =21 AND table1.start >= table2.start AND table1.end <= table2.end;

---

quote:

---

Originally posted by lasqlhelp

I see so I would simply type:

SELECT count(*) FROM table1, table2 WHERE table1.chrom IN 21 AND table1.start >= table2.start AND table1.end <= table2.end;

---

quote:

---

Originally posted by lasqlhelp

thanks for the help. it gave me an unexpected result in my two table i have

---

declare @test1 table
(
chrom int,
start int,
[end] int,
name varchar(10),
score decimal(10,4),
strand char(1)
)
declare @test2 table
(
chrom int,
start int,
[end] int,
name varchar(10),
score decimal(10,4),
strand char(1)
)

insert into @test1

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



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

--soln 1
select t1.chrom,count(*)
from @test1 t1
join @test2 t2
on t1.chrom=t2.chrom
where t1.start>=t2.start 
and t1.[end] <= t2.[end]
group by t1.chrom


--soln2
select t1.chrom,count(*)
from @test1 t1
join @test2 t2
on  t1.start>=t2.start 
and t1.[end] <= t2.[end]
where t2.chrom=21
group by t1.chrom

output
------------------
soln1
-----------------
chrom	count
21	4

soln2
--------------------
chrom	count
1	1
4	1
5	1
11	1
20	1
21	4
22	1

quote:

---

Originally posted by lasqlhelp

wow, looks great. I tried coping your first statement with the declare into my sql (I'm using sqlite) but it says sql error: near "declare": syntax error

any idea?

---

quote:

---

Originally posted by lasqlhelp

what do you use? that's free and compatible with my mac system?

---

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @test1 table

quote:

---

Originally posted by lasqlhelp

hi, installed mysql on my mac.

but when i copied the first 'declare statement' i get this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @test1 table

any ideas?

---