Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What kind of hierarchies would you want to build? Standard parent-child relationships are build in a single table. And hierarchies in that idea are on multiple levels (think manager-employees relations). What data structures do you like to model with your tables?
–
MarianNov 13 '12 at 18:36

2

You need to exaplain the relationship better, is it Table1 PARENT OF Table2 PARENT OF Table3?
–
FrustratedWithFormsDesignerNov 13 '12 at 18:48

@Marian, I want to build a hierarchy in which table1 stick with level 1, Table2 and Table3 as Level2 as level 3
–
zmkiNov 14 '12 at 12:53

excellent query but I needed an improvement if it were possible. And I have to read all this information in php and I have to pass first the father and correspondingly their children, and so and then I can move on to other parent. If I understand what I mean? Let's think in level 1 have the A and D, and at level 2 we have that A causes B and C and D do not originate anything. This query you created, first shows of all level 1 and level 2 of them then, and I wanted him to give me the level 1 when corresponding with their children. Got it?
–
zmkiNov 15 '12 at 10:54

@zmki: Oh that's a little different... Not sure the best way to do that. You might have to change the query in the WITH clause to use INNER JOINS between the tables instead of unions, but then you'd also have to think carefully about what data you select. Hmm... Or maybe you could add an ORDER BY in alltabs to correct it...?
–
FrustratedWithFormsDesignerNov 15 '12 at 15:38

This will work only with the additional assumption that the ids are unique accross the 3 tables.
–
Vincent MalgratNov 14 '12 at 9:42

@Leigh Riffel I wrote id and name in both tables, but for different tables they have different names. I'll edit the initial post to best see fit
–
zmkiNov 14 '12 at 13:01

Renaming the name column is a simple thing, but Vincent is correct - the ids must be unique across tables for this to work.
–
Leigh RiffelNov 14 '12 at 13:07

1

I'm sorry this does not work for you. What is different about your data or intended results that is not represented in the demonstration above? I created an SQL Fiddle for it as well -> sqlfiddle.com/#!4/423f7/1
–
Leigh RiffelNov 15 '12 at 13:12