problem with select

From: Adrian Johnson <oriolebaltimore(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: problem with select
Date: 2010-11-04 16:29:34
Message-ID: AANLkTikzh4r2R-bTxCBfEh_Q+DioQyRjEzgscBmVGeUz@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message André Fernandes 2010-11-04 16:45:15 Re: Linux
Previous Message Robert Gravsjö 2010-11-04 16:20:19 Re: Linux