Report Query: Selecting Parent for a child

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Report Query: Selecting Parent for a child

Author

Topic

PatriciaMarie_31
Starting Member

3 Posts

Posted - 2009-03-26 : 16:39:55

I am customizing reports out of our database. I need to add the parent org to the user.

By using the student table and the org relation table, i can mentally map the relationship to the parent org. (There are many child to a parent) Just can seem to figure out how to write the code.

if I select
s.stud_id
s.org
gives me the student org (with s=student table)

now I want to map this to the parent org on the org relation table (ort) and (ortp) for the parent

s.org = ort.org child

then select ortp.org parent

and of course how would I get this be the final org id that shows in the report.

Sorry, I am very new at this and have been able to teach myself how to add in a new column for supervisor since it was in the student table.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 16:49:43

It would be better if you give us some sample data and expected output.

PatriciaMarie_31
Starting Member

3 Posts

Posted - 2009-03-26 : 17:23:49

quote:
Originally posted by vijayisonly

It would be better if you give us some sample data and expected output.

Does this help?

Stud Table
Student ID: PMW
Name: Patricia
Org: Mom4

Stud ID: LJB
Name: Linda
Org: Mom2

Stud ID: DEB
Name: Dale
Org: Mom3

Stud ID: CPW
Org: Dad4

Org Relation Table
Parent Org: Mom
Org Child: Mom4

Parent Org: Mom
Org Child: Mom2

Parent Org: Mom
Org Child: Mom3

Parent Org: Dad
Org Child: Dad4

My report would produce (CSV)

Student Id, Student Name, Parent Org

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 17:29:36

You dint give the expected output...but maybe this

SELECT s.Student_Id,s.Student_Name,o.Parent_Org
FROM Stud_Table s INNER JOIN Org_Relation_Table o
ON s.Org = o.Org_Child

PatriciaMarie_31
Starting Member

3 Posts

Posted - 2009-03-26 : 17:57:06

This makes sense.

My output would only be a report (I don't create any other tables)

PMW, Patricia, Mom
LJB, Linda, Mom
CPW, Chris, Dad

I will give this a try to see what happens.

Thanks,
Patricia

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-29 : 03:25:51

[code]
select StudentID,studname,parentorg from @stud s
LEFT JOIN @orgrelation o on s.org = o.orgchild

try this too with cte
;with cte(StudentID,studname,org)
as
(
select StudentID,studname,org from @stud

union all
select studentid,studname,o.parentorg
from cte c
inner join
@orgrelation o on c.org = o.orgchild
)
select StudentID,studname,org
from (select *,row_number()over(partition by studname order by org) as rid from cte )s where rid = 1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources