How do i optimize this

[Buppy]

Hello,

I have a MySQL database with 3 tables. Each table contains a list of persons, and each table represents the level of a person. For example, tbl_level1_p, tbl_level2_p and tbl_level3_p. Level 1 persons have level 2 persons under them, and level 2 persons have level 3 persons under them. So, basically i neend to get a full list in an output form like this:

->Level 1 person data
->Level 2 person data
->Level 3 person data
->Level 3 person data
->Level 3 person data
->Level 2 person data
->Level 3 person data
->Level 3 person data

Then it does the same for next persons. What i got now is like 3 query loops:
SELECT * FROM tbl_level1_p
loop through tbl_level1_p
SELECT * FROM tbl_level2_p WHERE dataFromTbl1=something
loop through tbl_level2_p
SELECT * FROM tbl_level3_p WHERE dataFromTbl2=something
loop through tbl_level3_p
show data
end loop through tbl_level3_p
end loop through tbl_level2_p
end loop through tbl_level1_p

At this point it's hell lot of a loops and selects, and it is a bit too slow because the result set is quite large. Since i'm not that good with MySQL yet, i would like to know is if there is a better solution to this? Like, one advanced query, or maybe subselects, views, stored procedures, anything?

Thanks

[smantscheff]

You can do it all in one query and put the display logic into your program code.
Make sure that the fields on which the tables are linked are indexed. Also make sure that you select only the needed fields.

select t1.a,t1.b, t2.c,t2.d, t3.e, t3.f
from tbl_level1_p t1, tbl_level2_p t2, tbl_level3_p t3 
where t1.something=t2.something and t2.something_else = t3.something_else

[tinymark]

I'm probably misunderstanding something. Do you want all the permutations?

[tinymark]

Is this basically your setup?

tbl_level1_p		\\table name
person_level1_data	\\column name
Mike			\\Entry
Joe			\\Entry

tbl_level2_p		\\table name
person_level2_data	\\column name
Fred			\\Entry
Mark			\\Entry

tbl_level3_p		\\table name
person_level3_data	\\column name
Anne			\\Entry
Cathy			\\Entry