RE: any reply is eagerly awaited

[Muthukumar Selvarasu]

Hi

As per your table  first method (direct join)  won't help you


So use this


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'


But remember this if one of the join table fail you won't get that record in
the output list.  So make sure you database data (foreign field as well
data) proper.


Just place your fields list in the place of "(field as per your request)"

Best of luck.


Regards,
Muthukumar Selvarasu, 
Project Manager, Webmasters - Fijilive Group.

-----Original Message-----
From: saad khan [mailto:skm770@stripped] 
Sent: Friday, June 05, 2009 11:57 AM
To: Muthukumar Selvarasu; win32@stripped
Subject: Re: any reply is eagerly awaited

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
>>
>>
>>
>