Dear group:
I have a table structure like following:
city:
city_block age_from age_to name
SF 10 20 grade1
SF 21 30 grade1
SF 35 40 grade1
SF 53 19 grade2
SF 100 153 grade2
NY 20 21 grade5
mydata:
sample city_block age_from age_to baseo basen
1 SF 13 14 T Y
1 SF 33 34 A M
2 SF 24 25 G A
2 SF 18 19 G K
2 SF 33 34 A M
3 SF 13 14 T Y
3 SF 105 106 C T
I am interested in following result:
1. sample 1 and 3 share a same mydata.age_from and mydata.age_to (but
sample 2 and sample 3 should not have same age_from and age_to for
same city.name)
2. sample 1 and 2 share a same mydata.age_from and mydata.age_to
3. in the results basen should not be any of 'A' or 'T' or 'G' or 'C'.
that means for a give city.name sample 1 should contain both age_from
and age_to with sample 2 and sample 3. But sample 2 and sample 3
should have different age_from and age_to for same city.name.
myquery:
SELECT DISTINCT city.name from mydata,city WHERE mydata.sample = 1 AND
mydata.age_from >= city.age_from AND mydata.age_to <= city.age_to
INTERSECT
SELECT DISTINCT city.name from mydata,city WHERE mydata.sample = 2 AND
mydata.age_from >= city.age_from AND mydata.age_to <= city.age_to
INTERSECT
SELECT DISTINCT city.name from mydata,city WHERE mydata.sample = 3 AND
mydata.age_from >= city.age_from AND mydata.age_to <= city.age_to
INTERSECT AND
basen not in ('A', 'T', 'G','C');
I am not convinced that this is correct. can any one help me here please.
thanks
adrian
