Re: Help with queries. - Mailing list 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 | -     | RHPN293540
|chr19      | 38173254 | 38173420 | -     | RHPN293541 | chr19      | 38174563 | 38174720 | -     | RHPN293542 | chr19
   | 38176717 | 38176804 | -     | RHPN293543 | chr19      | 38178766 | 38178971 | -     | RHPN293544 | chr19      |
38182326| 38182456 | -     | RHPN293545 | chr19      | 38184987 | 38185154 | -     | RHPN293546 | chr19      | 38185553
|38185751 | -     | RHPN293547 | chr19      | 38190754 | 38190931 | -     | RHPN293548 | chr19      | 38194419 |
38194554| -     | RHPN293549 | chr19      | 38195387 | 38195475 | -     | RHPN293551 | chr19      | 38204311 | 38204397
|-     | RHPN293553 | chr19      | 38209244 | 38209383 | -     | RHPN293555 | chr19      | 38226989 | 38227115 | -
|RHPN293556 | chr19      | 38247524 | 38247603 | -     | RHPN2
 







Now taking:cto = 38162736 first row in above tablecfrom = 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
 

From this result, we get that all samples range from 1 to 7 (sample_id).




Now, from above result (snps table) I want to filter:
1. Those that have any letter in consbase other than ATGC (although in
this case there are no A or T or G or C they are there)
2. Those entries that have sample_id 1, 2 and 3.
3. Unique entries that have cfrom and cto common to 1 and 2  and 1 and
3 but not 2 and 3.

For example see below:  (say this is result X)gene  | sample_id | chromosome |  cfrom   |   cto
-------+-----------+------------+----------+----------+---------+----------RHPN2 |         2 | chr19      | 38178828 |
38178829RHPN2|         1 | chr19      | 38178828 | 38178829RHPN2 |         3 | chr19      | 38182424 | 38182425RHPN2 |
      1 | chr19      | 38182424 | 38182425
 

Here for gene RHPN2:
cfrom - cto (38178828 | 38178829) is common to samples 1 and 2
cfrom - cto ( 38182424| 38182425) is common to samples 1 and 3.

But both these samples 1 and 2   and  1 and 3 belong same gene (RHPN2 ).


Now I  have another table coverage, where  for sample positions above,
I have the following data:
(say this is result y)sample_id | chromosome |  cfrom   |   cto    | abase | tbase | gbase | cbase
-----------+------------+----------+----------+-------+-------+-------+-------        2 | chr19      | 38178828 |
38178829|     0 |    29 |     2 |    44        1 | chr19      | 38178828 | 38178829 |     0 |    52 |     0 |    32
  3 | chr19      | 38178828 | 38178829 |     0 |     0 |     0
 
|    28        1 | chr19      | 38182424 | 38182425 |    14 |     0 |    54 |     0        2 | chr19      | 38182424 |
38182425|    17 |     0 |    55 |     0        3 | chr19      | 38182424 | 38182425 |    13 |     0 |    26 |     0
 


Now from table snps, I want to get results that satisfy conditions
that gave result X  and combine this with results Y.

How is it possible to do this?

Thank you for your help.

Adrian.