Union into Join? How to combine?

[dani9]

hi, does anyone know how can I combine these two select statements without the Union? Can it be made?

SELECT [Level 1].SON_ID as DAD_ID, [Level 3].SON_ID as SON_ID

FROM ([Level 1] INNER JOIN ([Level 2] INNER JOIN [Level 3] ON [Level 2].SON_ID = [Level 3].DAD_ID) ON [Level 1].SON_ID = [Level 2].DAD_ID) INNER JOIN [unique MIS4] ON [Level 3].SON_ID = [unique MIS4].SON_ID;

UNION
SELECT [Level 2].SON_ID as DAD_ID, [Level 3].SON_ID as SON_ID

FROM ([Level 1] INNER JOIN ([Level 2] INNER JOIN [Level 3] ON [Level 2].SON_ID = [Level 3].DAD_ID) ON [Level 1].SON_ID = [Level 2].DAD_ID) INNER JOIN [unique MIS4] ON [Level 3].SON_ID = [unique MIS4].SON_ID;

 

[plog]

You can make one of them a Create Table query and the other an append table which adds to that newly created table.

Other than that, no.

Why do you have so many Level tables that hold the same data to begin with? Sounds like all this could be in the same table with a new field that holds level information.

 

[dani9]

Hi

I have up to 12 levels, and the son of one level is the father of another level... and i did it with union, is works until 9th levl and then it just shows error that there are to many tables or querys...

And with the append function i need like 66 querys and that is to much.

 

[plog]

Yeah, this all needs to go into one table. Possibly two, if are breaking the laws of nature.

I'm hoping that a son can only have one father, if that is true you basically create a table whose records reference one another. You assign every person in that table a unique id, Person_ID. Another field in each record is DAD_ID which contains another record's Person_ID. Now those two records are linked as father/son and you can run a query that links that table upon itself Joining one instance of the table on the Person_ID to the second instance of the table on the DAD_ID.

You can do that infinitely to get as many levels deep as you want. If a son can have multiple fathers, you are going to need another table to keep all that straight, but it would work essentially the same.

 

[spikepl]

@dani9

You have now received exactly the same advice as in you previous posting concerning your table structure. If you seek a different solution, at least tell ppl that you have got that advice, and chose not to follow it for specific reasons, so they do not have to reinvent the wheel, ok?

 

[boblarson]

I agree - the table structure appears to be horrible and in severe need of fixing before it gets to a point of being totally unwieldy using these "band-aid" solutions.

You don't need a table for each level!!!!

 

[dani9]

Thank you guys for replying, but i think i made my question clear, i just wanted to know if there is a way i can put the two select statements into one without the union... and plog answered it nice and simple, so thanks to Plog!

There is no way to put it together, so i am now trying to find another solution
to go around, i hope it works.

so basically if I describe level 4:
I have 3 tables, all of them with 2 columns, one is dad-id and another one is son-id,
and in one table i pick the son and the dad from level 4 (the dad from level 4 is son from table 3)
in second one i link son - level4 with dad in level 3 (this is the son from level 2) and in the third leve i link son from level 4 with the son from level 1.

So basically i need all the combinations of levels with level 4, like
level 1 - level 4
level 2 - level 4
level 3 - level 4

and in the end i Union them in one table. and that goes for all the levels.

and that would look like the end result with the 2 columns. And when i end up linking all the levels it stops with level 9, and won´t accept level 10, because there are to many tables to link, that is why i was trying to put 2 tables into one without the union.

I just wanted to explain what is it about, hope you see my problem now.

 

[JANR]

Looks like you need one table that self join onto itself to track heritage. I'm not good at explaning this consept but here is one that does: http://allenbrowne.com/ser-06.html

JR

 

[cimathers]

Why can you not create a table structure as follows: unless I have the wrong end of the stick?

LevelID Dad_ID Son_ID
1         11       22
2         22       33
3         33       44
4         44       55

You could then run relevant queries against that table. Obviously you would first need to transfer your existing tables into this structure using a query.

Chris

 

[dani9]

Cimaters, I have this kind of a table. Ok if a get deeper into my tables, I have one basic table called "Reference data original" with Son_ID, dad_ID, Lelevs from 1-12 and MIS LVL4 column at the end, and from this table i make one more table with distinct values of MIS LVL4 column.

And in this distinct table i have to link SON_ID with all upper levels as described before, but I also have to use the "reference data original" table, as some levels in between get lost with distinct.

So for each level i make such a query:

SELECT [Reference Data Original].DAD_ID, [Reference Data Original].SON_ID
FROM [Reference Data Original]
WHERE ((([Reference Data Original].LVL4)<>""));

and from this one I get all SON_ID from LVL4 linked to their DAD_ID´s.
And the i use the distinct MIS LVL4 table to select only the son_ID that i need and search for relevant levels.

Like this:

SELECT [Level 2].DAD_ID, [Level 4].SON_ID
FROM [Level 2] INNER JOIN ([Level 3] INNER JOIN ([Level 4] INNER JOIN [unique MIS4] ON [Level 4].SON_ID = [unique MIS4].SON_ID) ON [Level 3].SON_ID = [Level 4].DAD_ID) ON [Level 2].SON_ID = [Level 3].DAD_ID;

SELECT [Level 3].DAD_ID, [Level 4].SON_ID
FROM [Level 3] INNER JOIN ([Level 4] INNER JOIN [unique MIS4] ON [Level 4].SON_ID = [unique MIS4].SON_ID) ON [Level 3].SON_ID = [Level 4].DAD_ID;

SELECT [Level 4].DAD_ID, [Level 4].SON_ID
FROM [Level 4] INNER JOIN [unique MIS4] ON [Level 4].SON_ID = [unique MIS4].SON_ID;

And i put the 3 querys into one union for each level and in the end i Union all level unions it is complicated, i know, that is why I am searching for a simple solution :S

any ideas??

 

[dani9]

@ Janr:
I have tried this just now to see if it works, but it doesn´t work with my data. I have ID´s which end at level 4, level 7 or level 11.. depends on data. And if i put up 11 levels, i dont get the data from level 4 or 5 or 7 because he is trying to find them in level 11, and they are not there.

But, thank you anyway, for giving me the possible solution.