RE: any reply is eagerly awaited

[Muthukumar Selvarasu]

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