Selecting data from 7 tables

[phparion]

I have 7 tables in database. All of them shares a common coloumn called PMID.. i want to select data from all these 7 tables on the bases of PMID and am using the following query

$query = "Select * from pmstep1,pmstep2,pmstep3,pmstep4,pmstep5,pmusa,pmca 
     where pmstep1.pmid = pmstep2.pmid = pmstep3.pmid = pmstep4.pmid = pmstep5.pmid = pmca.pmid = pmusa.pmid ='shasha'";
 

but when i execute it on database using phpmyadmin. or command line to check my system hangs and it start to behave like 386 computers..

Please guide me that how can i select data based on this common coloumn from 7 tables. i never used JOINS and am not sure whether it has to do with joins or not.

thanks in advance

[py9jmas]

SELECT * 
FROM pmstep1,pmstep2,pmstep3,pmstep4,pmstep5,pmusa,pmca
WHERE pmstep1.pmid = 'shasha'
AND pmstep2.pmid = 'shasha'
AND pmstep3.pmid = 'shasha'
AND pmstep4.pmid = 'shasha'
AND pmstep5.pmid = 'shasha'
AND pmca.pmid = 'shasha'
AND pmusa.pmid = 'shasha';

Is how I'd do it. If the query is running slowly you may need to add indexes on the pmid columns.

I'm surprised the database accepted

 where pmstep1.pmid = pmstep2.pmid = pmstep3.pmid = pmstep4.pmid = pmstep5.pmid = pmca.pmid = pmusa.pmid ='shasha'

as valid syntax. Hopefully that's the cause of the problem.

[txbakers]

yes, it looks like the string of joins is the problem.

SQL syntax requires only one value to be matched per clause segment.

WHERE t1.field = t2.field AND t2.field = t3.field AND, etc.

You can also put and index on each table for the search field which will speed up the results.

[phparion]

well, you query took my pc on a long drive from where it was not willing to come back :D..
anyway i am now working on this query

Select * 
from pmstep1
INNER JOIN pmstep2
ON pmstep1.pmid = pmstep2.pmid
INNER JOIN pmstep3
ON pmstep2.pmid = pmstep3.pmid
INNER JOIN pmstep4
ON pmstep3.pmid = pmstep4.pmid
INNER JOIN pmstep5
ON pmstep4.pmid = pmstep5.pmid
INNER JOIN pmusa
ON pmstep5.pmid = pmusa.pmid
INNER JOIN pmca 
ON pmusa.pmid = pmca.pmid
AND pmusa.pmid ='shasha'

it is working for me with one problem, it selects PMID col from all the tables againa n again, i used GROUP BY PMID but it gives error.
anyway idea?

[Rufal]

Hi,
(not sure this applies to you but considering 7 joins, I figured some of them must apply.)
One method I employ extensively is to move one to one relationsships to an inline view in the select itself.

for instance a name table for id's.

i.e.
select
(select name from table1 where pid=t1.gid) as Group_name,
(select name from table2 where pid=t1.id) as item_name
item2,
item3
from table1 t1 , table2 t2, table3 t3
where t1.id=t2.t1id and t1.id=t3.t1id;

Note, that if you have a single driving table you only need to join fields to that table and not crossreference to every single table you are selecting from..

Regards

** edit **
Moving one-to-one joins to the select is a very nice way to optimize your query. since it removes extra analysis from the rdbms engine.