SQL Script to read and print child rows

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
rayluvsAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
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
 
rayluvsAuthor Commented:
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
 
tigin44Commented:
try this
SELECT ItemCode, Description, Cost, Price
FROM yourTable
WHERE ChieldParentRelate <> 0

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
The items MILK1GL, HOLSUM1B, SPEARMNT0 has no child, but should be included in the script.
0
 
tigin44Commented:
did you try my second post
0
 
POracleCommented:
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
 
SharathData EngineerCommented:
try this
select * 
  from YourTable
 where id not in (select ChildParentRelate from YourTable)

Open in new window

0
 
rayluvsAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.