Master-Detail query return on one row

0 pts.
Tags:
SQL
I have a parent-child relationship. I would like to have a query return the results where all children are on the same row as their parent. No children will have more than one parent. For instance: Table PARENT contains one column with the data: Parent1 Parent2 Parent3 Table CHILD contains two columns (foreign key to PARENT and the child column) with the data: Parent1, Child1 Parent1, Child2 Parent2, Child3 Parent3, Child4 Parent3, Child5 Parent3, Child6 I would like the query to present the results like: Parent1, Child1, Child2 Parent2, Child3 Parent3, Child4, Child5, Child6 Any help will be appreciated.
ASKED: March 24, 2005  9:01 AM
UPDATED: April 1, 2005  3:51 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,
I don’t know how to solve this with plain SQL, but if your dbms is Oracle, you can compile and run this simple PL/SQL procedure that will produce the desired output.

—-
CREATE OR REPLACE PROCEDURE Itk AS

TYPE IdTab IS TABLE OF child_table.child_id%TYPE;

CURSOR curs_1 IS
SELECT parent_id FROM parent_table ORDER BY parent_id;

rec_1 parent_table%ROWTYPE;

IdList IdTab;
IdString VARCHAR2(2500);

BEGIN
FOR rec_1 IN curs_1 LOOP

SELECT child_id BULK COLLECT INTO IdList FROM child_table WHERE parent_id = rec_1.parent_id;

IdString := ”;

FOR i IN 1..IdList.COUNT LOOP
IF i = 1 THEN
IdString := TO_CHAR(IdList(1));
ELSE
IdString := IdString || ‘,’ || TO_CHAR(IdList(i));
END IF;
END LOOP;

DBMS_Output.Put_Line( TO_CHAR(rec_1.parent_id) || ‘,’ || IdString );

END LOOP;
END Itk;

—-

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dejabu
    What you are asking for is a Cross Tab Query function as in MS Access. Oracle, Sybase MS SQL Server, to my knowledge, do not have a similar function and hence you would need to use a cursor similar to that detailed by previous response
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following