Hi all
Thanks for your concern I am attaching the db structure with this mail
kindly reply
All i want is that i should get all entries of a particular gene id from all
the tables
that is to say that if there is a gene id 3683 then i want all the data
related with this gene id from all other tables
kindly reply
drugs
Table comments: InnoDB free: 25600 kB
Field Type Null Default Comments
drug_id int(5) No
uniprot_id varchar(100) No
chemical_id varchar(20) No
chemical_name varchar(100) No
annotation varchar(200) No
stitchlink tinytext No
Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 6105 drug_id
chemical_id INDEX 1221 chemical_id
uniprot_id INDEX 1221 uniprot_id
Space usage:
Type Usage
Data 1,552 KiB
Index 409,600 B
Total 1,952 KiB
Row Statistics:
Statements Value
Format Compact
Rows 6,141
Row length ø 258
Row size ø 325 B
Next Autoindex 6,429
Creation May 27, 2009 at 06:54 AM
phpMyAdmin http://localhost/phpmyadmin/db_structure.php
1 of 5 5/28/2009 12:15 AM
genes
Table comments: InnoDB free: 25600 kB
Field Type Null Default Comments
gene_id varchar(50) No
geneid_link varchar(100) No
Gi varchar(100) No
hgnc varchar(100) No
hprd varchar(100) No
ensem varchar(100) No
ko varchar(100) No
def varchar(100) No
nuc_seq text No
prot_seq text No
Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 3074 gene_id
Space usage:
Type Usage
Data 10,768 KiB
Index 0 B
Total 10,768 KiB
Row Statistics:
Statements Value
Format Compact
Rows 3,877
Row length ø 2,844
Row size ø 2,844 B
Creation May 14, 2009 at 01:23 AM
phpMyAdmin http://localhost/phpmyadmin/db_structure.php
2 of 5 5/28/2009 12:15 AM
pathways
Table comments: InnoDB free: 25600 kB; (`gene_id`) REFER
`diabetes_mellitus/genes`(`gene_id`)
Field Type Null Default Comments
id2 int(10) No
gene_id varchar(100) No
pathway_id varchar(200) No
Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 1551 id2
gene_id INDEX 1551
gene_id
pathway_id
Space usage:
Type Usage
Data 229,376 B
Index 229,376 B
Total 458,752 B
Row Statistics:
Statements Value
Format Compact
Rows 1,609
Row length ø 142
Row size ø 285 B
Next Autoindex 1,549
Creation May 27, 2009 at 01:35 AM
phpMyAdmin http://localhost/phpmyadmin/db_structure.php
3 of 5 5/28/2009 12:15 AM
pdb
Table comments: InnoDB free: 25600 kB; (`gene_id`) REFER
`diabetes_mellitus/genes`(`gene_id`)
Field Type Null Default Comments
id1 int(10) No
gene_id varchar(100) No
pdb_id varchar(200) No
Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 2185 id1
gene_id INDEX 2185
gene_id
pdb_id
pdb_id INDEX 2185 pdb_id
Space usage:
Type Usage
Data 278,528 B
Index 557,056 B
Total 835,584 B
Row Statistics:
Statements Value
Format Compact
Rows 2,185
Row length ø 127
Row size ø 382 B
Next Autoindex 2,119
Creation May 27, 2009 at 01:34 AM
phpMyAdmin http://localhost/phpmyadmin/db_structure.php
4 of 5 5/28/2009 12:15 AM
protein
Table comments: InnoDB free: 25600 kB; (`gene_id`) REFER
`diabetes_mellitus/genes`(`gene_id`)
Field Type Null Default Comments
prot_id int(5) No
gene_id varchar(100) No
uniprot_id varchar(100) No
Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 7626 prot_id
gene_id INDEX 3813 gene_id
uniprot_id INDEX 7626 uniprot_id
Space usage:
Type Usage
Data 311,296 B
Index 425,984 B
Total 737,280 B
Row Statistics:
Statements Value
Format Compact
Rows 7,709
Row length ø 40
Row size ø 96 B
Next Autoindex 7,390
Creation May 27, 2009 at 01:31 AM
phpMyAdmin http://localhost/phpmyadmin/db_structure.php
5 of 5 5/28/2009 12:15 AM
- Show quoted text -
On Sun, May 31, 2009 at 4:20 PM, saad khan <skm770@stripped> wrote:
> Hi all
> Thanks for your concern I am attaching the db structure with this mail
> kindly reply
>
> All i want is that i should get all entries of a particular gene id from
> all the tables
> that is to say that if there is a gene id 3683 then i want all the data
> related with this gene id from all other tables
>
> kindly reply
>
>
> On Sat, May 30, 2009 at 10:02 AM, Muthukumar Selvarasu <
> muthukumar_se@stripped> wrote:
>
>> Hi
>>
>> Post your db structure and expected output
>>
>> As per you explanation, you may try out this way.
>>
>> SELECT (fields as per your request) FROM genes, protein, pathways, pdb
>> WHERE `genes`.`gene_id` = '3683'
>> AND protein.gene_id = `genes`.`gene_id`
>> AND pathways.gene_id = `genes`.`gene_id`
>> AND pdb.gene_id = `genes`.`gene_id`
>>
>> Remember that this give you many result since you using direct join
>>
>> Otherwise
>>
>> Use the same format by adding left out join if you want suppress no data.
>>
>> SELECT (fields as per your request) FROM genes
>>
>> LEFT OUTER JOIN protein ON protein.gene_id = `genes`.`gene_id`
>> LEFT OUTER JOIN pathways ON pathways.gene_id = `genes`.`gene_id`
>> LEFT OUTER JOIN pdb ON pdb.gene_id = `genes`.`gene_id`
>> WHERE `genes`.`gene_id` = '3683'
>>
>> Depends on your master child relationship you can change into LEFT OUTER
>> /RIGHT OUTER
>>
>>
>> Regards
>> Muthukumar Selvarasu,
>> Project Manager,
>> Fijilive Groups, Webmasters.
>>
>>
>>
>> -----Original Message-----
>> From: saad khan [mailto:skm770@stripped]
>> Sent: Saturday, May 30, 2009 5:07 AM
>> To: win32@stripped
>> Subject: any reply is eagerly awaited
>>
>> i had made a database on diabetes mellitus genes and protein information
>> earlier the data was already in a single table but due to ceratain issues
>> i
>> had to normalize it after i had nornmalized it i passed it certain queries
>> to which it gave me many combinations
>>
>> eg since earlier all the data was in single table if on the front end if
>> anybody searched for geneid he/she will get alll info abt that particular
>> gene pertaining to structure,pathway,drugs etc
>>
>> but now after normalizing it i dont know what query to pass so that on the
>> front end if a user enters a gene id he/she gets all the information
>> relating to that particular gene id
>>
>> i had tried many query statements including those involving left outer
>> joins
>> and inner join but to no avail
>>
>> following are the cardinalities in the data
>>
>> one gene -many proteins(zero or many)
>> one gene - many pathways(zero or many)
>> one gene -many pdb structures(zero or many) one protein - many drugs(zero
>> or many
>>
>>
>> a query like this returns many results and sometimes puts the server down
>>
>> SELECT `genes`.`gene_id`, `protein`.`uniprot_id`, `pathways`.`pathway_id`
>> FROM genes, protein, pathways, pdb WHERE (`genes`.`gene_id` = '3683')
>>
>>
>> moreover i also want to know if i should include gene_id in drugs table as
>> foregn key
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe:
>> http://lists.mysql.com/win32?unsub=1
>>
>>
>>
>
