CREATE TABLE score (
gene_name CHAR(15),
score_g2d REAL,
score_dgp REAL,
score_pocus REAL)
SELECT t4.gene_name,
COALESCE(g2d.score,0),
COALESCE(dgp.score,0),
COALESCE(pocus.score,0)
FROM t4
LEFT JOIN g2d ON t4.gene_name=g2d.gene_name
LEFT JOIN dgp ON t4.gene_name=dgp.gene_name
LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;Change the column types to whatever is appropriate for your situation.
Michael
Jeroen Van Goey wrote:
Hey all,
I've three tables (g2d, dgp and pocus), with a gene-name, a score and some other information:
mysql> select * from dgp; +--------+---------------+--------------+------------------------------+------------+--------+ | chr | start | stop | gene_name | score | strand | +--------+---------------+--------------+------------------------------+------------+--------+ | chr13 | 69810099 | 70239331 | ENSG00000165659 | 738.681 | - | | chr13 | 64676788 | 65600573 | ENSG00000184226 | 627.447 | - | | chr13 | 59781822 | 59787282 | ENSG00000150506 | 619.467 | + | ....
And I've a table with a lot of gene-name's. Now I want to combine these 4 tables into a fifth table, named score, wich list in the first column all the gene names, and in the three succesive columns the score for that gene from the g2d, dgp, and pocus tables, if a score is found. Otherwise the default value should be 0.00 Like this:
mysql> select * from score;
+------------------------------+-----------------+------------------+----------------------+
| gene_name | score_g2d | score_dgp | score_pocus | +------------------------------+-----------------+------------------+----------------------+
| ENSG00000165659 | 0.000 | 738.681 | 0.00 |
| ENSG00000184226 | 123,123 | 627.447 | 0.00 |
| ENSG00000999999 | 0.000 | 0.00 | 987,987 |
......
How should I construct my query? I've been trying for hours now, and can't get it rigth. Thanks in advance.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
