Getting all the child records

Question

Hi,

I am using SQL Query and below are the tables.

Organization

OrgID     Name             RAOID      SubGroupID
1         Org RAO1         2          NULL
2         Org RAO2         NULL       2
3         Org Sub Group1   3          NULL
4         Org RAO3         NULL       1
5         Org RAO4         1          NULL
6         Org Sub Group2   NULL       3

RAO

RAOID   RAOGID
1         1
2         1

Sub Group

SubGroupID  RAOID
1            1
2            1
3            1
4            2

I have three tables Organisation, RAO, SubGroup. I can have organisations either of type RAO or SubGroup. However if you see the subgroup table it is having multiple RAOID. I want all the subgroup organisation name under rao when any rao orgid is passed to query.

Answer 1

Hi Guys,

I solved my problem with below query

CREATE PROCEDURE uspGetSubSource
( @ORGID INT)
AS
DECLARE @RAOID INT
SET @RAOID = (SELECT RAOID FROM tblOrganisation WHERE ORGID = @ORGID)

IF @RAOID IS NOT NULL
BEGIN
SELECT tblOrganisation.ORGID,
tblOrganisation.NAME as SUBSOURCENAME FROM tblOrganisation 
LEFT OUTER JOIN tblSubGroup ON tblOrganisation.SubGroupID = tblSubGroup.SubGroupID
WHERE
tblSubGroup.RAOID = @RAOID
END

Cheers!!