Joining Query Results To Exploded Query Results (w/o Cursor?)

Question

Post reply

Joining Query Results To Exploded Query Results (w/o Cursor?)

larsosman56

SSC Enthusiast

Points: 111
More actions
January 1, 2009 at 9:08 pm

#77167

Hoping for some assistance. I have been working this for weeks, looking into recursive CTEs, cursors, etc.
My first query returns a result set, but I want to pass the results to a second query (in effect as a parameter) which would return multiple rows per 1 row of of the original result set, and possibly join to the original result set. I have the code that generates both results (the first query and the exploded result set). My issue is the structure of the query. I may end up going the cursor route.
First Query Results
A
B
C
D
What I Would Like:
A A1
A A2
A A3
B B1
B B2
B B3
C C1
etc.
I don't think this is the solution
SELECT X,Y FROM first_table
JOIN
(SELECT Y FROM second_table) AS Z
ON ...
Is this making sense?
My thanks!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

larsosman56 SSC Enthusiast Points: 111 More actions · Answer 1

Sorry! I should have posted this to the 2K5 section!

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Try this

select t1.col1, t2.col1 from

(your first query) as t1

cross join

(your second query) as t2

order by 1

Madhivanan

Failing to plan is Planning to fail

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

larsosman56 (1/1/2009)
Hoping for some assistance. I have been working this for weeks, looking into recursive CTEs, cursors, etc.
My first query returns a result set, but I want to pass the results to a second query (in effect as a parameter) which would return multiple rows per 1 row of of the original result set, and possibly join to the original result set. I have the code that generates both results (the first query and the exploded result set). My issue is the structure of the query. I may end up going the cursor route.

There isn't really enough information here. Can you post your first query? Also, where do the numbers come from in your expected output? See the link below for guidance.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

larsosman56 SSC Enthusiast Points: 111 More actions · Answer 4

Sorry ChrisM. I really didn't give you much to work with. Table 1 has Parent data, and a UDF returns subordinates to the ParentID passed to it. The UDF returns a table.

The first query is a snap of course:

SELECT ParentID from Parents

with results:

ParentID

---------

TOP_PARENT

PARENT_1

PARENT_2

PARENT_3

NEXT_PARENT

The UDF would have this usage:

SELECT * FROM fnGetSubordinates('TOP_PARENT")

The UDF returns two columns:

ParentID SubordinateID

-------- -------------

TOP_PARENT PARENT_1

TOP_PARENT PARENT_2

TOP_PARENT PARENT_3

TOP_PARENT NEXT_PARENT

The problem is that I need to call the UDF for every parent returned in the first query to build out a resultset that has every possible dependent for every parent, looking something like this:

TOP_PARENT PARENT_1

TOP_PARENT PARENT_2

TOP_PARENT PARENT_3

TOP_PARENT NEXT_PARENT

PARENT_1 PARENT_2

PARENT_1 PARENT_3

PARENT_1 NEXT_PARENT

PARENT_2 PARENT_3

PARENT_2 NEXT_PARENT

...

Thanks again for the assistance. I am this closer to a plain old WHILE loop to walk the Parents table.

-- L

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

larsosman56 (1/2/2009)
Sorry ChrisM. I really didn't give you much to work with. Table 1 has Parent data, and a UDF returns subordinates to the ParentID passed to it. The UDF returns a table.
...
Thanks again for the assistance. I am this closer to a plain old WHILE loop to walk the Parents table.
-- L

No worries L.

You refer to two tables, Table1 and Parents. Please confirm.

This is an example of recursion. There are some excellent articles on the forum about recursion using CTE's, and it ain't my strong point (yet) so I'll go stand in a corner while someone more experienced steps in. Have a scan around the forum, and also check out the link in my sig, which will encourage you to post more information including your existing code and table structures.

CTE's make recursion a breeze, you will be surprised.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden