Query help

Question

Post reply

Query help

Megha P

SSCommitted

Points: 1976
More actions
June 11, 2014 at 4:11 am

#294256

Hi , i need help in below query,i have posted just a sample data original table has many records.
Here is the ddl
--Below is ddl statement
select x.* into #temp
FROM
(
SELECT '6062' UserID,'2' fk_RoleId,'1' OrganizationId,NULL TeacherId UNION ALL
SELECT '6063' UserID,'3' fk_RoleId,'1' OrganizationId,'6062' TeacherId UNION ALL
SELECT '6066' UserID,'3' fk_RoleId,'1' OrganizationId,'6062' TeacherId UNION ALL
SELECT '6064' UserID,'4' fk_RoleId,'1' OrganizationId,'6063' TeacherId UNION ALL
SELECT '6067' UserID,'4' fk_RoleId,'1' OrganizationId,'6066' TeacherId ) X
select * from #temp
Below is required result
select 6062 userid,6064 student UNION ALL
select 6062 userid,6067 student UNION ALL
select 6063 userid,6064 student UNION ALL
select 6066 userid,6067 student
Below is what i have tried but not working 🙁
;with CTE
AS
( select userid studentid ,teacherid userid from #temp where fk_roleid = 3 and teacherID is not null
UNION ALL
select B.userid as studentid,e.studentid AS userid from cte e inner join #temp B on e.studentid = B.teacherid
)
select userid,studentid from CTE
Here , relationship is like pediatrition --> parent --> student
and in result i need pediatrition--> student and parent --> student
fk_RoleId = 2 (pediatrition)
fk_RoleId = 3 (parent)
fk_RoleId = 4 (student)
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

So what you want returned is something like this:

Pediatrician Parent Student

6062 6063 6064

6062 6066 6067

Or

Pediatrician Person Relationship

6062 6064 Pediatrician - Student

6062 6067 Pediatrician - Student

6063 6064 Parent - Student

6066 6067 Parent- Student

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

If you only need those 3 levels, just keep it simple. 😉

SELECT p.TeacherId AS userID,

s.UserID AS student

FROM #temp p

JOIN #temp s ON p.UserID = s.TeacherID

WHERE p.fk_RoleId = '3'

AND p.TeacherId IS NOT NULL

UNION ALL

SELECT p.UserID,

s.UserID

FROM #temp p

JOIN #temp s ON p.UserID = s.TeacherID

WHERE p.fk_RoleId = '3'

AND p.TeacherId IS NOT NULL

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Megha P SSCommitted Points: 1976 More actions · Answer 3

Jack Corbett (6/11/2014)
So what you want returned is something like this:
Pediatrician Parent Student
6062 6063 6064
6062 6066 6067
Or
Pediatrician Person Relationship
6062 6064 Pediatrician - Student
6062 6067 Pediatrician - Student
6063 6064 Parent - Student
6066 6067 Parent- Student

Required result is

useridstudent

60626064

60626067

60636064

60666067

Thanks..

Megha P SSCommitted Points: 1976 More actions · Answer 4

Luis Cazares (6/11/2014)
If you only need those 3 levels, just keep it simple. 😉
SELECT p.TeacherId AS userID,
s.UserID AS student
FROM #temp p
JOIN #temp s ON p.UserID = s.TeacherID
WHERE p.fk_RoleId = '3'
AND p.TeacherId IS NOT NULL
UNION ALL
SELECT p.UserID,
s.UserID
FROM #temp p
JOIN #temp s ON p.UserID = s.TeacherID
WHERE p.fk_RoleId = '3'
AND p.TeacherId IS NOT NULL

This is exactly what i am looking for..:-) and its done in very simple way ..i was making it more complex..

Thanks Luis Cazares