Need Some Help with an Odd Query
-
Hello Everyone
OK, I am going to do my best to explain this and not leave anything out.
In the table below, there are parentID's and ChildID's In some rows there are
the same ChildID also being stored in the ParentID column, this will indicate a
grandchildID.
For example, in row 2311 the ChildID is 22407, and then in row 2314, that same number(22407) is in the ParentID column.
The ChildID (22418) in row 2314 is the number that that I need, along with the ParentID and the RowID
I am not sure how to go about creating an effecient query to select the rows that are needed. I hope the examples help you to determine what I am attempting to explain.
Can anyone lend a hand with this rather odd query?
DECLARE @ParentChild AS TABLE
(
RowID INT
, ParentID INT
, ChildID INT
, WantThisRow VARCHAR(3)
)
DECLARE @GrandChild AS TABLE
(
RowID INT
, ChildParentIDINT
, GrandchildID INT
)
INSERT INTO @ParentChild
(RowID, ParentID, ChildID, WantThisRow)
SELECT 2310, 22402, 22403, '' UNION ALL
SELECT 2311, 22406, 22407, '' UNION ALL --<
SELECT 2312, 22410, 22411, '' UNION ALL
SELECT 2313, 22413, 22414, '' UNION ALL --<
SELECT 2314, 22407, 22418, 'Yes' UNION ALL -- Want this Row
SELECT 2315, 22419, 22420, '' UNION ALL
SELECT 2316, 22414, 22423, 'Yes' UNION ALL -- Want this Row
SELECT 2317, 22426, 22427, '' UNION ALL
SELECT 2318, 22429, 22430, '' UNION ALL --<
SELECT 2319, 22432, 22433, '' UNION ALL
SELECT 2320, 22430, 22435, 'Yes' UNION ALL -- Want this Row
SELECT 2321, 22436, 22437, '' UNION ALL
SELECT 2322, 22438, 22439, '' UNION ALL
SELECT 2323, 22440, 22441, '' UNION ALL --<
SELECT 2324, 22441, 22444, 'Yes' UNION ALL --< Want this Row
SELECT 2325, 22444, 22446, 'Yes' -- Want this Row
SELECT * FROM @ParentChild
INSERT INTO @GrandChild
(RowID, ChildParentID, GrandchildID)
SELECT 2314, 22407, 22418 UNION ALL
SELECT 2316, 22414, 22423 UNION ALL
SELECT 2320, 22430, 22435 UNION ALL
SELECT 2324, 22441, 22444 UNION ALL
SELECT 2325, 22444, 22446
SELECT * FROM @GrandChild
--< this indicates the row that the ChildID is the ParentID, but is the ParentID of the GrandchildID
Thank you in advance to everyone for your time and help.
Andrew SQLDBA
-
Is this what you want?
SELECT x.RowID, x.ParentID AS ChildParentID, x.ChildID AS GrandchildID
FROM @ParentChild x
WHERE EXISTS (SELECT * FROM @ParentChild y WHERE x.ParentID=y.ChildID)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
DECLARE @ParentChild AS TABLE(
RowID INT
, ParentID INT
, ChildID INT
, WantThisRow VARCHAR(3)
)
DECLARE @GrandChild AS TABLE
(
RowID INT
, ChildParentIDINT
, GrandchildID INT
)
INSERT INTO @ParentChild
(RowID, ParentID, ChildID, WantThisRow)
SELECT 2310, 22402, 22403, '' UNION ALL
SELECT 2311, 22406, 22407, '' UNION ALL --<
SELECT 2312, 22410, 22411, '' UNION ALL
SELECT 2313, 22413, 22414, '' UNION ALL --<
SELECT 2314, 22407, 22418, 'Yes' UNION ALL -- Want this Row
SELECT 2315, 22419, 22420, '' UNION ALL
SELECT 2316, 22414, 22423, 'Yes' UNION ALL -- Want this Row
SELECT 2317, 22426, 22427, '' UNION ALL
SELECT 2318, 22429, 22430, '' UNION ALL --<
SELECT 2319, 22432, 22433, '' UNION ALL
SELECT 2320, 22430, 22435, 'Yes' UNION ALL -- Want this Row
SELECT 2321, 22436, 22437, '' UNION ALL
SELECT 2322, 22438, 22439, '' UNION ALL
SELECT 2323, 22440, 22441, '' UNION ALL --<
SELECT 2324, 22441, 22444, 'Yes' UNION ALL --< Want this Row
SELECT 2325, 22444, 22446, 'Yes' -- Want this Row
SELECT P1.RowID,P1.ParentId,P1.ChildID,P1.WantThisRow FROM
@ParentChild P1
INNER JOIN @ParentChild P2
ON P1.ParentID = P2.ChildID
INSERT INTO @GrandChild
(RowID, ChildParentID, GrandchildID)
SELECT P1.RowID,P1.ParentId,P1.ChildID FROM
@ParentChild P1
INNER JOIN @ParentChild P2
ON P1.ParentID = P2.ChildID
SELECT * FROM @GrandChild
EDIT: Just seen the other one. Both should work..
-
Join the table to itself on ParentID = ChildID. At least, that's what it looks like to me.
The table is poorly designed. An adjacency hierarchy should have ParentID or ChildID, not both. What happens if they get out of sync in a table like this? You could easily have a ChildID in one row with a different ParentID in the target row. If you can, redesign the table to use a one-way relationship.
SELECT PC2.*FROM @ParentChild AS PC1
INNER JOIN @ParentChild AS PC2
ON PC1.ChildID = PC2.ParentID ;
See if that gets you what you need, for now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thank You to everyone. I just could not wrap my mind around the self-join. It is a slow moving monday for me, after a long weekend of moving into a new house. That in itself will just suck the life out of you.
All the queries worked perfectly. I know that is a horrible table design. You should see the rest of the database. None of it is good. I have told the decision makers, and they told me to plan to design it correctly. I am happy about that.
Andrew SQLDBA
-
Yes, the good old, "going to work to rest up from life" type situation. Been there many times.
Hope the move went well and was for a positive reason.
Definitely good when management has your back on something like refactoring a database into a better design. I've had both directions on that. "We hired you because you're the expert. Do what you think is necessary, we'll back you up on it." vs "It works. Don't touch it. Just fix the things that we notice are broken. We don't care that it'll have constant problems and will be progressively slower as time goes on. If you don't shut up about that..." Sounds like you have a version of the good one on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply