pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Help with queries. 3+ messages / 2 participants [nested] [flat]
* Help with queries. @ 2010-09-21 11:57 Adrian Johnson <oriolebaltimore@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Johnson @ 2010-09-21 11:57 UTC (permalink / raw) To: pgsql-sql Dear group, I just started learning postgres and I have to analyze my data. So please bear with me, for all the simple questions that I am asking. Apologies. I have a table: snps table id | sample_id | chromosome | from | to | ---------------------------------------------------------------- 1 1 chr1 10 11 2 1 chr1 14 15 3 2 chr1 14 15 4 2 chr1 9 10 5 3 chr1 14 15 6 3 chr14 35 36 7 3 chr14 39 40 8 2 chr14 39 40 9 2 chr14 43 44 10 1 chr14 43 44 gene table: id | chromosome | from | to | genename 1 chr1 4 20 SRC 2 chr1 25 45 SRC 3 chr1 80 100 CSK 4 chr1 120 140 CSK My aim is to for a gene in gene table (SRC that has two ranges 4-20 and 25-45), I want to map all coordinates in snps table. Then I want to get those entries where sample 1 and sample 2 have in common and sample 1 and sampl3 in common. I do not know how to get these entries. Could any one help me please. thank you. adrian ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with queries. @ 2010-09-21 16:11 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> parent: Adrian Johnson <oriolebaltimore@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Oliveiros d'Azevedo Cristina @ 2010-09-21 16:11 UTC (permalink / raw) To: Adrian Johnson <oriolebaltimore@gmail.com>; pgsql-sql Howdy, Adrian, Please see my questions below > snps table > > id | sample_id | chromosome | from | to | > ---------------------------------------------------------------- > 1 1 chr1 10 11 > 2 1 chr1 14 15 > 3 2 chr1 14 15 > 4 2 chr1 9 10 > 5 3 chr1 14 15 > 6 3 chr14 35 36 > 7 3 chr14 39 40 > 8 2 chr14 39 40 > 9 2 chr14 43 44 > 10 1 chr14 43 44 > > > gene table: > > id | chromosome | from | to | genename > 1 chr1 4 20 SRC > 2 chr1 25 45 SRC > 3 chr1 80 100 CSK > 4 chr1 120 140 CSK > > My aim is to for a gene in gene table (SRC that has two ranges 4-20 and > 25-45), > I want to map all coordinates in snps table. Please advice me, What do you call coordinates to, exactly? Sorry, my genetics knowledge is very poor. You call the from and to fields coordinates? Do they have the same meaning as the from and to fields from table snps ? What does it mean to map all coordinates in table snps? Do you wanna know which entries from snps map in the SRC gene? Coordinates are the segment where the gene/sample fit in the chromosome? Is my understanding correct ? > Then I want to get those entries where sample 1 and sample 2 have in > common and sample 1 and sampl3 in common. > Could you give an example of your desired output, for the data you have ? > > I do not know how to get these entries. Could any one help me please. > > thank you. > > adrian > Best, Oliver > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with queries. @ 2010-09-21 23:16 Adrian Johnson <oriolebaltimore@gmail.com> parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> 0 siblings, 0 replies; 3+ messages in thread From: Adrian Johnson @ 2010-09-21 23:16 UTC (permalink / raw) To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql Hello Oliver. thanks for your reply. Here are my answers. Sorry I shot e-mail in morning and I was not clear about it. I am afraid, I am not jamming you with all information. Thank you very much for your help. ====================================== your questions ==================================== First, answers to questions you asked: > Please advice me, > What do you call coordinates to, exactly? Sorry, my genetics knowledge is > very poor. == I call coordinates cto - crom > You call the from and to fields coordinates? ==yes > Do they have the same meaning as the from and to fields from table snps ? == cfrom - cto fields have yes. > What does it mean to map all coordinates in table snps? == Means I want to take snps.cfrom and snps.cto and gene.gene by checking if they are in range of gene.cfrom and gene.cto from gene table. > Do you wanna know which entries from snps map in the SRC gene? == yes and more than that. > Coordinates are the segment where the gene/sample fit in the chromosome? Is > my understanding correct ? == yes I am calling on "chr19" (cfrom - cto) (38162736 - 38163007) are coordinates. =================================== illustration of example ========================== I know basic queries, but I am finding it difficult to get what I am describing below. I am illustrating an example here . Here I query for a gene RHPN2 from gene table: gid | chromosome | cfrom | cto | frame | gene -------+------------+----------+----------+-------+------- 93538 | chr19 | 38162736 | 38163007 | - | RHPN2 93540 | chr19 | 38173254 | 38173420 | - | RHPN2 93541 | chr19 | 38174563 | 38174720 | - | RHPN2 93542 | chr19 | 38176717 | 38176804 | - | RHPN2 93543 | chr19 | 38178766 | 38178971 | - | RHPN2 93544 | chr19 | 38182326 | 38182456 | - | RHPN2 93545 | chr19 | 38184987 | 38185154 | - | RHPN2 93546 | chr19 | 38185553 | 38185751 | - | RHPN2 93547 | chr19 | 38190754 | 38190931 | - | RHPN2 93548 | chr19 | 38194419 | 38194554 | - | RHPN2 93549 | chr19 | 38195387 | 38195475 | - | RHPN2 93551 | chr19 | 38204311 | 38204397 | - | RHPN2 93553 | chr19 | 38209244 | 38209383 | - | RHPN2 93555 | chr19 | 38226989 | 38227115 | - | RHPN2 93556 | chr19 | 38247524 | 38247603 | - | RHPN2 Now taking: cto = 38162736 first row in above table cfrom = 38247603 last row in above table I want to get all those entries from snp table # select sample_id, chromosome,cfrom, cto, refbase, consbase from snps where cto > 38162736 and cfrom < 38247603 and chromosome = 'chr19' ; sample_id | chromosome | cfrom | cto | refbase | consbase -----------+------------+----------+----------+---------+---------- 2 | chr19 | 38178828 | 38178829 | C | Y 5 | chr19 | 38182405 | 38182406 | T | Y 5 | chr19 | 38182424 | 38182425 | G | R 5 | chr19 | 38185101 | 38185102 | C | Y 3 | chr19 | 38182424 | 38182425 | G | R 1 | chr19 | 38178828 | 38178829 | C | Y 1 | chr19 | 38182424 | 38182425 | G | R 7 | chr19 | 38185101 | 38185102 | C | Y 6 | chr19 | 38185101 | 38185102 | C | Y 4 | chr19 | 38178828 | 38178829 | C | Y 4 | chr19 | 38182424 | 38182425 | G | R ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-09-21 23:16 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-09-21 11:57 Help with queries. Adrian Johnson <oriolebaltimore@gmail.com> 2010-09-21 16:11 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> 2010-09-21 23:16 ` Adrian Johnson <oriolebaltimore@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox