Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

derived query

Posted on 2007-04-09
5
Medium Priority
?
293 Views
Last Modified: 2008-02-01
Hi,
I have a table CH in SQL Server 2005 with columns ParentID (PK) and childID. ChildID could be the ParentID of Another ChildID. I want all the ChildID that are also the ParentIDs.

I have tried this:
Select ParentID From  (Select ChildID from ch) ch
   
But it does not work.
0
Comment
Question by:SA4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 9

Accepted Solution

by:
gpompe earned 1000 total points
ID: 18878502
Try this
select a1.ChildID from Ch a1 join Ch a2 on a1.ChildId=a2.ParentID
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18878510
please try this:

WITH R ( ParentID, ChildID, Level )
AS (
   SELECT ParentID, ChildID, 0
   FROM CH
   WHERE ParentID IS NULl
UNION ALL
   SELECT c.ParentID, c.ChildID, R.Level + 1
   FROM R
   JOIN CH
     ON R.ChildID = CH.ParentID
   )
SELECT *
FROM R

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp
0
 

Author Comment

by:SA4
ID: 18878786
thanks for the reply, I tried this:
WITH R ( ParentID, ChildID, Level )
AS (
   SELECT ParentID, ChildID, 0
   FROM CH
   WHERE ParentID IS NULl
UNION ALL
   SELECT c.ParentID, c.ChildID, R.Level + 1
   FROM R
   JOIN CH
     ON R.ChildID = CH.ParentID
   )
SELECT *
FROM R

But It does not give any result And I know that there are a few that I should be getting.
 
0
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 1000 total points
ID: 18879366
select parentid from ch where parentid in (select cihldid from ch)
0
 

Author Comment

by:SA4
ID: 18883354
thanks for your help.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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