Search Postgresql Archives

problem with select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux