October 27, 2010 at 9:44 am
Any help would be appreciated. Classic parent/child relationship.
Ideally I would want my flag field to be at the parent level but due to application/security issues I need to put the flag on the child.
The logic I am trying to implement is that if one child is flagged 'Y' then all other children with the same parent should also be considered to be flagged as Y as well.
So that SELECT ChildID, ParentID, Flag FROM Child WHERE ParentID = 2 would return
21,2,Y
22,2,Y
23,2,Y
not
21,2,N
22,2,N
23,2,Y
CREATE TABLE Parent (ParentID int)
INSERT INTO Parent
SELECT 1 UNION SELECT 2 UNION SELECT 3
CREATE TABLE Child (ChildID int,ParentID int, Flag char(1))
INSERT INTO Child
SELECT 10,1,'N' UNION
SELECT 12,1,'N' UNION
SELECT 21,2,'N' UNION
SELECT 22,2,'N' UNION
SELECT 23,2,'Y'
SELECT * FROM Parent
SELECT * FROM Child
SELECT * FROM Child WHERE ParentID = 2
DROP TABLE Parent
DROP TABLE Child
October 27, 2010 at 9:51 am
Here's one option......
SELECT c.ChildID,
c.ParentID,
c2.Flag
FROMChild c
LEFT JOIN (
SELECT ParentID,
CASE WHEN SUM(CASE WHEN Flag = 'Y' THEN 1 ELSE 0 END) >= 1 THEN 'Y' ELSE 'N' END as Flag
FROMChild
GROUP BY ParentID
) c2 ON c.ParentID = c2.ParentID
WHEREc.ParentID = 2
October 27, 2010 at 9:53 am
You'll pretty much have to crawl the hierarchy three times. Once to find the parent, once to check if any child of that parent has a Y, and once to return your dataset.
Have you looked into whether a nested sets hierarchy model will do what you need hierarchy-wise? If so, the queries will be much, much faster and more efficient.
- 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
October 27, 2010 at 10:01 am
October 27, 2010 at 10:05 am
I haven't looked at a hierarchy model. The child to parent ratio is fairly low, 5:1 on average.
I was thinking I could do an existence check in a subquery.
pseudo code to follow...
SELECT
ChildID,
ParentID,
If the following exists then 'Y'
(Select top 1 from PARENT where ParentID = 2 AND flag = 'Y')
ELSE 'N'
FROM Child WHERE ParentID = 2
October 27, 2010 at 10:11 am
John Rowan (10/27/2010)
Am I missing something Gus?
Your solution will work if the hierarchy only has two levels. To be precise, it will work if a child can't have a child. Anything more complex will require proportionately more processing.
- 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
October 27, 2010 at 10:16 am
Thanks Gus, your correct.
Chrissy321, You mentioned that you can't put the flag at the parent level because application/security reasons. Rarely have I seen where outside factors like app security determine relational schema design. Can you elaborate on this? I would venture to say that you can still put it on the parent and use a view to get around whatever restrictions you are facing.
October 27, 2010 at 10:20 am
GSquared (10/27/2010)
John Rowan (10/27/2010)
Am I missing something Gus?Your solution will work if the hierarchy only has two levels. To be precise, it will work if a child can't have a child. Anything more complex will require proportionately more processing.
Yes there are only two levels.
October 27, 2010 at 10:24 am
If there are only two levels, then yes, and Exists check will get you what you need.
SELECT
ID,
CASE WHEN EXISTS ( SELECT
1
FROM
MyTable AS MT2
WHERE
ParentID = @ParentID
AND FlagColumn = 'Y' ) THEN 'Y'
ELSE 'N'
END AS Flag
FROM
MyTable AS MT
WHERE
ParentID = @ParentID ;
Or something like 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
October 27, 2010 at 10:29 am
John Rowan (10/27/2010)
Thanks Gus, your correct.Chrissy321, You mentioned that you can't put the flag at the parent level because application/security reasons. Rarely have I seen where outside factors like app security determine relational schema design. Can you elaborate on this? I would venture to say that you can still put it on the parent and use a view to get around whatever restrictions you are facing.
It a vendor provided app where I can add custom fields to either the parent or the child.
Business rules state that the users who need to maintain the flag can not update the parent but can update the children. I could have my users update all the child records but I am trying to ease their data entry burden if possible by implementing logic where the update of one child record is the equivalent of updating all child records.
>>Rarely have I seen where outside factors like app security determine relational schema design.
Makes sense but not in this case since my app is vendor provided and my control is limited.
October 27, 2010 at 2:59 pm
The problem I see with that (Joe's) query is it assumes collation and values will always produce the Y result. Can you be certain of that? If, for example, the flag is changed to -1 and 0 at some point, using Max() won't necessarily work on it.
- 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
October 27, 2010 at 3:11 pm
Thnaks All. I was able to reach a suitable solution using the CASE WHEN EXISTS syntax!
October 28, 2010 at 2:50 am
CELKO (10/27/2010)
CREATE VIEW MarkedChildren (parent_id, child_id, [font="Arial Black"]silly_flag[/font])
You just can't resist, can ya? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2010 at 2:51 am
Chrissy321 (10/27/2010)
Thnaks All. I was able to reach a suitable solution using the CASE WHEN EXISTS syntax!
That's cool... can you post your final code, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2010 at 10:06 am
Jeff Moden (10/28/2010)
Chrissy321 (10/27/2010)
Thnaks All. I was able to reach a suitable solution using the CASE WHEN EXISTS syntax!That's cool... can you post your final code, please?
My employer strongly discourages that so often I'll post sample data structure sufficient to illustrate the problem and then modify the sample solution to my needs.
This doesn't match my sample or real data so I can't say if its syntactically correct but rather representative of the actual solution.
SELECT
COALESCE
(
CASE WHEN EXISTS
(
SELECT 1 FROM Parent P WHERE P.Parent_CD = Child.Parent_CD AND P.Parent_CD IN
(SELECT DISTINCT P.Parent_CD FROM Parent P INNER JOIN Child C ON C.Parent_CD = P.Parent_CD INNER JOIN ChildCustom AS CC ON C.Child_ID = CC.Child_ID WHERE CC.SillyFlag= 'Y')
)
THEN 'MyCode'
ELSE NULL
END,
other stuff....
)
FROM ....
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply