retrieve data from multiple tables referencing some tables in mysql
- by I Like PHP
i have 10 tables have innoDB engine
1. one is state_table which attributes are state_id and state_name
2. another table city_table which attributes are city_id and city_name
3. one more table permit_table which attribute is p_id
above city_id,state_id and permit_id is references to rest of 7 tables.
each table having state_id, city_id and permit_id referencing above tables
now i want to extract all tables data with their respective city name and state name ( each tables may have different city id and state id)
i m using below mysql query( i know it's very length way.... ) .
please tell me how to do it with optimized method?
SELECT p.*,cp.city_name,sp.state_name,
o.*,co.city_name,so.state_name,
t.*,ct.city_name,st.state_name,
th.*,cth.city_name,sth.state_name,
f.*,cf.city_name,sf.state_name
.......so on................
.......so on................
............................
FROM permit_table p
JOIN table_city cp ON cp.city_id=p.city_id
JOIN table_state sp ON sp.state_id=p.state_id
JOIN table_one o ON o.permit_id=p.permit_id
JOIN table_city co ON co.city_id=o.city_id
JOIN table_state so ON so.state_id=o.state_id
JOIN table_two t ON t.permit_id=p.permit_id
JOIN table_city ct ON ct.city_id=t.city_id
JOIN table_state st ON st.state_id=t.state_id
JOIN table_three th ON th.permit_id=p.permit_id
JOIN table_city cth ON cth.city_id=th.city_id
JOIN table_state sth ON sth.state_id=th.state_id
JOIN table_four f ON f.permit_id=p.permit_id
JOIN table_city cf ON cf.city_id=f.city_id
JOIN table_state sf ON sf.state_id=f.state_id
................so on.........................
................so on.........................
..............................................
WHERE p.permit_id=base64_encode(mysql_real_escape_string($_GET[pid]));
Thanks For help me always.
