Getting data from multiple tables

[aztec]

Hello

 

I am using PHP with a MySQL database.

 

I have multiple tables in the database each with the same column names.

 

There is a FID column which contains a unique number across all the database.

 

The data in each row contains a link to the table above, i.e. table9 is linked to table10 etc.

 

At this time I am using three queries to get data from three different tables.

 

Question 1:- how do I start to construct a query that will get data from three tables given that the column names for each table are the same.

 

Question 2:- is it more economical on the server to run one complex query than three small queries.

 

Kind Regards

[scvinodkumar]

create an alias for common field

 

for example, if the common field in all three tables is FID then use like this

 

select t1.FID "f1",t2.FID "f2",t3.FID "f3" from table1 t1,table2 t2,table3 t3...

 

i hope this is solve your problem...

 

 

[aztec]

Hello

 

All of the fields are common fields, the content of FID is unique across the database. In each table there is a link to FID from the table number below, ie table 9 has a link to the FID of table 10 and table 10 has a link to table 11.

 

Regards

[redarrow]

First thing can we please see the tables from the database please...

 

post your database structure please...........

 

using a join is defently faster then using three seprate database querys........

 

Saying that if the database is a stand alone database run on it own server,then there be

no real diffrence to the preformance off the database server, but it will diffently make

your life easer as a programmer to use joins in this current project.........

 

 

[aztec]

Hello

 

This is my table structure and is common acros the database

 

id is auto increment and is table based.

fid is unique number across the database

 

id  	int(11)
Fid  	int(10)
first_name  	varchar(255)
second_name  	varchar(255)
surname  	varchar(255)
mf  	text
dob  	date
pob  	varchar(40)
dod  	date
pod  	varchar(40)
burial  	varchar(40)
dom  	date
spouses  	int(4)
link_to_spouse
link_to_fid  	int(4)

 

 

Regards

[redarrow]

Are you telling me you got that database structure three times

as tables in the database.........

 

if so what the table names please.....

[aztec]

Hello

 

The table names start at gen_5 and repeat up to gen_12.  Each table holds the data of a generation of family members.

 

So at this time there are 8 tables in the database each the same structure but each holding different data

 

Regards

[zenag]

this might help you...

"select ti.fieldname,t2.fieldname,t3.fieldname from t1 left join t2 on t.id=t2.id left join t3 on t2.id=t3.id"

 

 

here id is common fieldname in three tables and have commom values.....

[zenag]

you can also select multiple fieldnames from many tables using this query..

[redarrow]

Here a join off three tables ok

 

you add the next 5 lol good luck.........

 

<?php

$sql="SELECT
table1.id, 
table1.Fid, 
table1.first_name,  	
table1.second_name,  
table1.surname,  	
table1.mf,  	
table1.dob,  	
table1.pob,  	
table1.dod,  	
table1.pod,  	
table1.burial,  	
table1.dom,  	
table1.spouses,  	
table1.link_to_spouse,
table1.link_to_fid as one,
table2.id, 
table2.Fid, 
table2.first_name,  	
table2.second_name,  
table2.surname,  	
table2.mf,  	
table2.dob,  	
table2.pob,  	
table2.dod,  	
table2.pod,  	
table2.burial,  	
table2.dom,  	
table2.spouses,  	
table2.link_to_spouse,
table2.link_to_fid as two,
table3.id, 
table3.Fid, 
table3.first_name,  	
table3.second_name,  
table3.surname,  	
table3.mf,  	
table3.dob,  	
table3.pob, 	
table3.dod,  	
table3.pod,  	
table3.burial,  	
table3.dom,  	
table3.spouses,  	
table3.link_to_spouse,
table3.link_to_fid as three,
FROM one,two,three
WHERE one.Fid=two.Fid AND two.Fid=three.Fid
"; 
$sql_result=mysql_query($sql)or die(mysql_error()); 	
?>

[zenag]

sorry friend  its not ti ..its t1...

select t1.fieldname,t2.fieldname,t3.fieldname from t1 left join t2 on t1.id=t2.id left join t3 on t2.id=t3.id"

 

 

[redarrow]

zenag would this work aswell

 

<?php

$sql="SELECT
table1.id, 
table1.Fid, 
table1.first_name,  	
table1.second_name,  
table1.surname,  	
table1.mf,  	
table1.dob,  	
table1.pob,  	
table1.dod,  	
table1.pod,  	
table1.burial,  	
table1.dom,  	
table1.spouses,  	
table1.link_to_spouse,
table1.link_to_fid as one,
table2.id, 
table2.Fid, 
table2.first_name,  	
table2.second_name,  
table2.surname,  	
table2.mf,  	
table2.dob,  	
table2.pob,  	
table2.dod,  	
table2.pod,  	
table2.burial,  	
table2.dom,  	
table2.spouses,  	
table2.link_to_spouse,
table2.link_to_fid as two,
table3.id, 
table3.Fid, 
table3.first_name,  	
table3.second_name,  
table3.surname,  	
table3.mf,  	
table3.dob,  	
table3.pob, 	
table3.dod,  	
table3.pod,  	
table3.burial,  	
table3.dom,  	
table3.spouses,  	
table3.link_to_spouse,
table3.link_to_fid as three,
FROM one,two,three
WHERE one.Fid=two.Fid AND two.Fid=three.Fid
"; 
$sql_result=mysql_query($sql)or die(mysql_error()); 	
?>

[aztec]

Hello

 

Thanks to all of you who posted replies to my question. I have not yet tried them out but after I have done some testing I will post a reply.

 

Regards

[aztec]

Hello

 

Using the code provided by redarrow I get the following error message:-

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM one,two,three WHERE one.Fid=two.Fid AND two.Fid=three.Fid'

 

I have only changed the table1, table2 and table3 to the actual table name in the database.

 

Regards