| View previous topic :: View next topic |
| Author |
Message |
ofer71
Intermediate
Joined: 12 Feb 2003
Posts: 358
Topics: 4
Location: Israel
|
 Posted: Wed Jun 30, 2004 7:01 am Post subject: Parent-child relationships |
 |
|
Hi all
Please help with the following issue:
I have 2 tables, parent and child - like the following example:
| Code: |
Parent:
CLIENT_ID CLIENT_NAME
------------ ----------------------
1111111 AAAAAAAA
2222222 BBBBBBBBB
Child:
CLIENT_ID ADDRESS
------------ ---------------
1111111 ADDRESS1A
1111111 ADDRESS1B
1111111 ADDRESS1C
2222222 ADDRESS2A
2222222 ADDRESS2B
2222222 ADDRESS2C
|
- Each record in the parent table connected to exactly 3 records in the child table.
The expected output is like this:
| Code: |
CLIENT_ID CLIENT_NAME ADDRESS(A) ADDRESS(B) ADDRESS(C)
---------- -------------- ------------- -------------- -------------------
1111111 AAAAAAAA ADDRESS1A ADDRESS1B ADDRESS1C
2222222 BBBBBBBB ADDRESS2A ADDRESS2B ADDRESS2C
|
That is - a "flat" view of the 1 parent record & 3 child records.
Can this be achived using single-simple SQL query ?
Thanks in advance -
O.
________
Subaru Rex history
Last edited by ofer71 on Sat Feb 05, 2011 11:17 am; edited 1 time in total |
|
| Back to top |
|
 |
Manas Biswal
Intermediate
Joined: 29 Nov 2002
Posts: 382
Topics: 27
Location: Chennai, India
|
| Posted: Wed Jun 30, 2004 8:29 am Post subject: |
|
|
ofer71,
I would have first of all changed the structure of my child table....
Regards,
Manas |
|
| Back to top |
|
|
ofer71
Intermediate
Joined: 12 Feb 2003
Posts: 358
Topics: 4
Location: Israel
|
| Posted: Wed Jun 30, 2004 8:34 am Post subject: |
|
|
Sorry, can't be done. This is a client's table that not going to be changed.
________
Honda XR600 history
Last edited by ofer71 on Sat Feb 05, 2011 11:17 am; edited 1 time in total |
|
| Back to top |
|
|
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
| Posted: Wed Jun 30, 2004 2:59 pm Post subject: |
|
|
ofer71, Are there any other columns in child table. If yes, what are they ? Is it oracle /db2 ?
Am asking based on the assumption that the values ADDRESS1A ,ADDRESS1B , ADDRESS1C you have listed for ADDRESS are not to be treated literally and are just a way of representation. |
|
| Back to top |
|
|
ofer71
Intermediate
Joined: 12 Feb 2003
Posts: 358
Topics: 4
Location: Israel
|
| Posted: Wed Jun 30, 2004 3:04 pm Post subject: |
|
|
Hi
There are other columns in the child table. One of them is occurence.
A little explanation: The parent & child tables used to be a single file in a hierarchical database. The child table derived from a multi-occurence field in the old file, and is logically connected by CLIENT_ID to its original record.
And no, it's not Oracle.
________
RZ350
Last edited by ofer71 on Sat Feb 05, 2011 11:17 am; edited 1 time in total |
|
| Back to top |
|
|
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
| Posted: Wed Jun 30, 2004 3:14 pm Post subject: |
|
|
| ofer71, what exactly does occurence contain .. is it like 1,2,3 ? |
|
| Back to top |
|
|
ofer71
Intermediate
Joined: 12 Feb 2003
Posts: 358
Topics: 4
Location: Israel
|
| Posted: Wed Jun 30, 2004 3:15 pm Post subject: |
|
|
Yes.
________
cheap glass bongs
Last edited by ofer71 on Sat Feb 05, 2011 11:17 am; edited 1 time in total |
|
| Back to top |
|
|
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
| Posted: Wed Jun 30, 2004 3:21 pm Post subject: |
|
|
Try this ..
| Code: |
Select P.CLIENT_ID
,P.CLIENT_NAME
,A.ADDRESS
,B.ADDRESS
,C.ADDRESS
From Parent P
,Child A
,Child B
,Child C
Where P.CLIENT_ID = A.CLIENT_ID
And A.OCCURENCE = 1
And P.CLIENT_ID = B.CLIENT_ID
And B.OCCURENCE = 2
And P.CLIENT_ID = C.CLIENT_ID
And C.OCCURENCE = 3
;
|
|
|
| Back to top |
|
|
Manas Biswal
Intermediate
Joined: 29 Nov 2002
Posts: 382
Topics: 27
Location: Chennai, India
|
| Posted: Thu Jul 01, 2004 9:36 am Post subject: |
|
|
ofer71,
What is the primary key of the CHILD table. This is DB2 or Oracle?
Regards,
Manas |
|
| Back to top |
|
|
ofer71
Intermediate
Joined: 12 Feb 2003
Posts: 358
Topics: 4
Location: Israel
|
| Posted: Thu Jul 01, 2004 9:54 am Post subject: |
|
|
My primary key is CLIENT_ID + OCCURENCE.
And again: DB2.
________
buy volcano vaporizer
Last edited by ofer71 on Sat Feb 05, 2011 11:18 am; edited 1 time in total |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 01, 2004 10:11 am Post subject: |
|
|
ofer71,
Did you try Vini's suggestion. It sure works and gives you the desired results.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
ofer71
Intermediate
Joined: 12 Feb 2003
Posts: 358
Topics: 4
Location: Israel
|
| Posted: Thu Jul 01, 2004 10:22 am Post subject: |
|
|
Yes, it did.
Thanks for all the help.
O.
________
roor bong
Last edited by ofer71 on Sat Feb 05, 2011 11:18 am; edited 1 time in total |
|
| Back to top |
|
|
Anand_R
Intermediate
Joined: 24 Dec 2002
Posts: 189
Topics: 60
|
| Posted: Fri Jul 02, 2004 3:57 am Post subject: |
|
|
Hi Vini,
I didn't get the concept of 'OCCURENCE'. Is it the column name in the table given by OFER or the part of SQL command. Can you please give me the link for detailed explanation for OCCURENCE. I was trying to find it in SQL refence. but no luck..
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri Jul 02, 2004 4:45 am Post subject: |
|
|
Anand_r,
Did you read the following posts by ofer71?
| Quote: |
There are other columns in the child table. One of them is occurence.
|
| Quote: |
My primary key is CLIENT_ID + OCCURENCE.
|
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
