SELECT statement question

[michael123]

I have three tables, table1 store records which may have next level ID(child), table2 lists corresponding child ID, and table3 stores the possible message for that particular ID.

table1:

------------------------------------------
ID	|	group	|	tochild
------------------------------------------
1		A		y
2		A		n
3		B		y
4		A		y
5		A		n

table2:

----------------------------
ID	|	childID	|	
----------------------------
1		3	
1		2	
3		5
4		2

table3:

--------------------------------
ID	|	message
--------------------------------
2		message for ID2
4		message for ID4
5		message for ID5

Now I want to populate message for selected ID (both current level and sub level if exist), for example, if "ID=1", how to display the following list? how to select table recursively for sub-level?

-----------------------------------
ID	|	message		
-----------------------------------
2		message for ID2
5		message for ID5

[chrisbliss18]

Your tables are designed very poorly. I'm not even able to follow which table relates to which other table.

An easy to follow table design has an ID field that has values that are unique, non-repeating, and refer to the records in that table and the table itself has a meaniningful name. When you make fields that will hold ID values that relate to other tables, name them as such (parentID, childID, etc). For example, remake your tables as such:

Parent (ID, group, toChild (this field confuses me; it doesn't seem to have a purpose))
Child (ID, parentID)
Message (ID, childID)

If your tables were designed like that, it would be very easy to understand what you are trying to do, but as they are, I cannot follow what you are wanting.