Retrieve data from multiple tables that refer to certain tables in mysql

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,
        ............................
        .......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
          ..............................................
          ................so on.........................
          ..............................................

 WHERE p.permit_id=base64_encode(mysql_real_escape_string($_GET[pid]));

Thanks to all for helping me always.

Don’t think of the tables as having to join EACH OTHER. They just have to join the conglomeration. So rather than adding the “JOIN table_city” and “JOIN table_state” so many times, just make sure that the earlier joins are correct, and when necessary join through the others. For example, you can say JOIN table_two ON t.permit_id = p.permit_id AND t.city_id = p.city_id AND ...

Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Retrieve data from multiple tables that refer to certain tables in mysql

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录