Forum Moderators: open

Message Too Old, No Replies

Tree structure in MySQL

Tree structure in MySQL with TWO parameters

         

Genyk

12:35 pm on Mar 19, 2007 (gmt 0)

10+ Year Member



Hello, I'm developing some php/mysql site with news feeds and found problem:
suppose we have three tables with structure:
1. id, ..., geo_id, category_id, ..., ... - news table
2. id, parent_id, title - regions table
3. id, parent_id, title - categories table

2 table represent tree structure like
1 Europe
2 -France (parent_id=1)
3 -GB (parent_id=1)
4 --England (parent_id=3)
5 --Ireland (parent_id=3)

3 table represent tree structure like
1 Incidents
2 -Catastrophes (parent_id=1)
3 -Crime (parent_id=1)

Suppose I need to show news from GB about Crime
In case of linear structure of regions and categories tables query will be like that:
SELECT * FROM news WHERE geo_id=3 AND category_id=3
but I also need to include in results all England and Ireland news :(
Than suppose that Crime category have some children and I going down...
If You have some ideas, please tell me how to solve this problem,
and I'm sorry for my bad english, it is not my native language.