[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Script to read and print child rows

Posted on 2009-12-16
9
Medium Priority
?
179 Views
Last Modified: 2012-05-08
I have a table where there are items in which there is a relation of parent and child.  By this I mean that a product, like CASE of Coor's is the parent of the products of 6pk Coor's and 1 can Coor's (see attached).  

To make the relationship between the Parent/Child, if the column "ChildParentRelate" has a value, that measn the that item has a parent.  The vakue is the ID of the parent.

What need a query to display the child items only if the item being read has a child, as in the example Coors and Bud (see attached).  The rest of the items has no child, it displayed as they are.

In the attachment, the first part displays the database table content, the second part display expected script results


eeChildParent.pdf
0
Comment
Question by:rayluvs
9 Comments
 

Author Comment

by:rayluvs
ID: 26060552
corrected the PDF (it was highlighting a child).  The yellow represent the parent in the first part, and thier child in the second part of the PDF (for the purpose to demonstrate the relation between the items)
eeChildParent.pdf
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26060567
try this
SELECT ItemCode, Description, Cost, Price
FROM yourTable
WHERE ChieldParentRelate <> 0

Open in new window

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 26060585
so this should give you the result
SELECT ItemCode, Description, Cost, Price
FROM yourTable
WHERE ChieldParentRelate <> 0 
UNION
SELECT ItemCode, Description, Cost, Price
FROM yourTable Y
WHERE ChieldParentRelate = 0
  AND NOT EXISTS (SELECT NULL FROM yourTable T WHERE Y.id = T.ChieldParentRelate AND T.ChieldParentRelate <> 0 )

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:rayluvs
ID: 26060619
The query should display the items that has no child and the child, it should not display the parents as the PDF demonstrate.  That is the items displayed based on my table should be:

ItemCode         Description
MILK1GL         MILK 1 GALLON
HOLSUM1B     HOULSUM BREAD
COORS12PK   COORS' 12 PK
COORS6PK     COORS' 6 PK
COORS1CAN  COORS' 1 CAN
SPEARMNT0   SPEARMINT GUM
BUD6PK          BUD 6 PK
BUD1CAN       BUD 1 CAN

Noticed that the items here are non-parents, only child items and items that has no child
0
 

Author Comment

by:rayluvs
ID: 26060625
The items MILK1GL, HOLSUM1B, SPEARMNT0 has no child, but should be included in the script.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26060690
did you try my second post
0
 
LVL 8

Expert Comment

by:POracle
ID: 26060731
Hi,

You can try this,

SELECT * FROM [YourTableName]
WHERE ID not in ( SELECT isnull(ChildParentRelate,0)  FROM  [YourTableName]  )

I Hope, this will help you.......
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26060762
try this
select * 
  from YourTable
 where id not in (select ChildParentRelate from YourTable)

Open in new window

0
 

Author Comment

by:rayluvs
ID: 26108193
Hi tigin44, yes it did.  Somehow I jumped over that date (sorry about that).   POracle and Sharath_123 only displays one child.

I'll proceed to close it.  Thanx

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question