[SOLVED] Help Retrieving data from multiple tables

[mrjonnytou]

I have an edit form which I would like to populate with data from three tables (person, person_history and position)

 

The MySQL tables/fields are:

 

person_table:

id

frst_name

last_name

position_id

 

position_table:

id

position

 

person_history:

id

position_id

person_id

date_from

date_to

 

I have already created an edit form and extracted data from a single table but I am struggling when it comes to linking records from multiple tables.

 

Any help with the queries would be much appreciated.

[bluejay002]

this doesn't seem to be a PHP post but I guess I'll post... I hope modo can move this right after.

 

but I dont have a single idea what data you want to get. also, position_id is existing in both person_table and person_history. person_history should be enough: (so neglecting, position_id in person_table)

 

SELECT DISTINCT specify_fields_here, ...

FROM person_table as pet, person_history as peh, position_table pot

WHERE pet.id = peh.person_id AND peh.position_id = pot.id

 

[mrjonnytou]

Hi,

 

Thanks for your post,

 

The data I'm trying to retrieve is first_name, last_name, position, date_from and date_to.

 

 

[bluejay002]

then...

 

SELECT DISTINCT pet.first_name,

pet.last_name,

pot.position,

peh.date_from,

peh.date_to

FROM person_table as pet,

person_history as peh,

position_table pot

WHERE pet.id = peh.person_id

AND peh.position_id = pot.id

 

actually there are a lot of ways to do this... this is just one, the simplest that is.

 

again, i recommend you to remove position_id from person_table unless you intend to keep a fast track of the current position (plus point) but eats some memory (minus point).

[mrjonnytou]

Thanks for your help, it works!

 

Another example i found seemed to work by using joins. Are there any advantages of using one way over the other?

 

The other working example:

 

SELECT first_name, last_name, position, date_from, date_to

FROM person

JOIN person_history

ON person.person_id = person_history.person_id

JOIN position

ON person_history.position_id = position.position_id

[bluejay002]

the example above itself is a join... inner join to be specific. in terms of difference in performance... well, i haven't thought of that. I used the example I gave you when I first learned query. Though currently, am using much more like the one you provided since its easier to mod when your changing from one type of join to another, say left join for example.