Help Needed in Relational Logic

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  May 7, 2015 at 7:48 pm

  #317305

  Hi,

  Sample data to play with

  use Temp

  Go

  declare @master table(ID int, EmplopyeeID int);

  declare @Releation table (IdRelation int idenity(1,1) primary key, ParentID int,ChildID int)

  declare @Logic table(ID int,name varchar(20),company varchar(20),Salary money,ValidUser bit);

  insert into @master(ID)

  select 1,100 union all

  select 2 ,200 union all

  select 3 ,300 union all

  select 4 ,400;

  insert into @Releation(ParentID,ChildID)

  select 1,2 union all

  select 3,4

  insert into @Logic(ID,name,company,Salary,ValidUser)

  select 1,'James','Microsoft',1000,1 union all

  select 3,'JamesTrend','Xerox',2000,1;

  With ExpectedResult as (ID,EmployeeID,company,Salary,ValidUser,Relation) as

  (

  select 1,100,'James','Microsoft',1000,1,'Parent' union all

  select 2,200,'James','Microsoft',1000,1,'Child' union all

  select 3,300,'JamesTrend','Xerox',2000,1,'Parent' union all

  select 4,400,'JamesTrend','Xerox',2000,1,'Child' union all

  )

  select * from ExpectedResult

  Basically i need to get the ID from @logic table and map it with relation table,Master table and get EmployeeId,Relation. Also i need parent and child have

  same company,Salary,ValidUser values.

  I am not sure how to achieved this. I tried in different ways. I don't want to use cursor/loop.

  Can anyone please help me on this tough logic with sample script.

• Jeff Moden

  SSC Guru

  Points: 1003895

  More actions

  May 7, 2015 at 8:27 pm

  #1796450

  born2achieve (5/7/2015)

  ---

  Hi,

  Sample data to play with

  use Temp

  Go

  declare @master table(ID int, EmplopyeeID int);

  declare @Releation table (IdRelation int idenity(1,1) primary key, ParentID int,ChildID int)

  declare @Logic table(ID int,name varchar(20),company varchar(20),Salary money,ValidUser bit);

  insert into @master(ID)

  select 1,100 union all

  select 2 ,200 union all

  select 3 ,300 union all

  select 4 ,400;

  insert into @Releation(ParentID,ChildID)

  select 1,2 union all

  select 3,4

  insert into @Logic(ID,name,company,Salary,ValidUser)

  select 1,'James','Microsoft',1000,1 union all

  select 3,'JamesTrend','Xerox',2000,1;

  With ExpectedResult as (ID,EmployeeID,company,Salary,ValidUser,Relation) as

  (

  select 1,100,'James','Microsoft',1000,1,'Parent' union all

  select 2,200,'James','Microsoft',1000,1,'Child' union all

  select 3,300,'JamesTrend','Xerox',2000,1,'Parent' union all

  select 4,400,'JamesTrend','Xerox',2000,1,'Child' union all

  )

  select * from ExpectedResult

  Basically i need to get the ID from @logic table and map it with relation table,Master table and get EmployeeId,Relation. Also i need parent and child have

  same company,Salary,ValidUser values.

  I am not sure how to achieved this. I tried in different ways. I don't want to use cursor/loop.

  Can anyone please help me on this tough logic with sample script.

  It's not tough logic. You'd have gotten it if you started to whittle at it.

  SELECT m.ID, m.EmplopyeeID, l.name, l.company, l.Salary, l.ValidUser,

  Relation = CASE WHEN m.ID = r.ParentID THEN 'Parent' ELSE 'Child' END

  FROM @Logic l

  JOIN @Releation r on r.ParentID = l.ID

  JOIN @master m on (m.ID = r.ParentID OR m.ID = r.ChildID)

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  May 8, 2015 at 4:44 am

  #1796504

  thank you jeff for your time on this. below is my version. But your version is much more cleaner and straight forward.

  ;WITH Rel AS

  (

  SELECT ParentId, name,company,Salary,ValidUser, ChildID

  FROM @Releation R

  JOIN @Logic L ON R.ParentID = L.ID

  UNION ALL

  SELECT R.ChildId, Rel.name, Rel.company,Rel.Salary,Rel.ValidUser, R.ParentID

  FROM @Releation R

  JOIN Rel ON Rel.parentId = R.ParentId

  )

  SELECT M.ID,M.EmplopyeeID,

  name

  ,company,Salary,ValidUser,

  (SELECT CASE WHEN ParentID = M.ID THEN 'Parent'

  WHEN ChildID = M.ID THEN 'Child' END

  FROM @Releation WHERE (M.ID = ChildID OR M.ID = ParentID)) AS 'Relation'

  FROM @master M

  INNER JOIN Rel ON rel.ParentID = M.ID

  ORDER BY ID

• ralm

  Hall of Fame

  Points: 3156

  More actions

  May 8, 2015 at 7:07 am

  #1796554

  Select M.*,L.Name,L.Company,L.Salary,L.ValidUser, Case when M.ID = R.ParentId then 'Parent' else 'Child' end as Relation

  from @Logic L Join @Releation R On L.Id =R.ParentId

  Join @master M On M.ID in (R.ChildId,R.ParentID)

  ^{[font="Verdana"]Regards,
  Rals[/font].}

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  May 8, 2015 at 9:11 am

  #1796618

  thankns Ralls