RE: any reply is eagerly awaited

[John Bonnett]

The query you have shown below is creating what is called a "full outer join" of the 4
tables in it filtered by the single where clause. This means possibly only one row from
the genes table but every possible combination of rows from the other tables. It will
usually give you a lot of rows as output and is rarely used in practice.

If I correctly understand the DB structure you have created then you probably want
something like this (i am just guessing at your column names).

SELECT `genes`.`gene_id`, `protein`.`uniprot_id`, `pathways`.`pathway_id`
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`

The extra conditions in the WHERE clause effectively create "inner joins" between the
tables and restrict the the output rows to only those that are really relevant. One
problem is that if there are no matching rows in one of the tables then any output for
that gene is suppressed. You can use left and right outer joins to get around that if
necessary. 

To be more helpful we really need to know the structure of your tables and what sort of
data you want to extract.

John Bonnett



-----Original Message-----
From: saad khan [mailto:skm770@stripped]
Sent: Sat 30/05/2009 02:36
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