From: Date: June 5 2009 11:35am Subject: RE: any reply is eagerly awaited List-Archive: http://lists.mysql.com/win32/19017 Message-Id: <002901c9e5c1$008fe950$01afbbf0$@com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 =3D `genes`.`gene_id` LEFT OUTER JOIN pathways ON pathways.gene_id =3D `genes`.`gene_id` LEFT OUTER JOIN pdb ON pdb.gene_id =3D `genes`.`gene_id` WHERE `genes`.`gene_id` =3D '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,=20 Project Manager, Webmasters - Fijilive Group. -----Original Message----- From: saad khan [mailto:skm770@stripped]=20 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 =F8 258 Row size =F8 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 =F8 2,844 Row size =F8 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 =F8 142 Row size =F8 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 =F8 127 Row size =F8 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 =F8 40 Row size =F8 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 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=20 > from all the tables that is to say that if there is a gene id 3683=20 > then i want all the data related with this gene id from all other=20 > tables > > kindly reply > > > On Sat, May 30, 2009 at 10:02 AM, Muthukumar Selvarasu <=20 > 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,=20 >> pdb WHERE `genes`.`gene_id` =3D '3683' >> AND protein.gene_id =3D `genes`.`gene_id` AND pathways.gene_id =3D=20 >> `genes`.`gene_id` AND pdb.gene_id =3D `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 =3D `genes`.`gene_id` LEFT = >> OUTER JOIN pathways ON pathways.gene_id =3D `genes`.`gene_id` LEFT=20 >> OUTER JOIN pdb ON pdb.gene_id =3D `genes`.`gene_id` WHERE=20 >> `genes`.`gene_id` =3D '3683' >> >> Depends on your master child relationship you can change into LEFT=20 >> 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=20 >> 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=20 >> 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=20 >> particular gene pertaining to structure,pathway,drugs etc >> >> but now after normalizing it i dont know what query to pass so that=20 >> on the front end if a user enters a gene id he/she gets all the=20 >> information relating to that particular gene id >> >> i had tried many query statements including those involving left=20 >> 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=20 >> or many) one gene -many pdb structures(zero or many) one protein -=20 >> many drugs(zero or many >> >> >> a query like this returns many results and sometimes puts the server=20 >> down >> >> SELECT `genes`.`gene_id`, `protein`.`uniprot_id`,=20 >> `pathways`.`pathway_id` FROM genes, protein, pathways, pdb WHERE=20 >> (`genes`.`gene_id` =3D '3683') >> >> >> moreover i also want to know if i should include gene_id in drugs=20 >> table as foregn key >> >> -- >> MySQL Windows Mailing List >> For list archives: http://lists.mysql.com/win32 To unsubscribe: >> http://lists.mysql.com/win32?unsub=3Dmuthukumar_se@stripped >> >> >> >