Two Foreign key from same table ,select query require?[Answered] RSS

• • Reply
  akhterr

  Member

  116 Points

  274 Posts

  Two Foreign key from same table ,select query require?

  Feb 04, 2020 05:46 AM|akhterr|LINK

  I want below output from CrossMatch Table where i am inserting two BID from Group table,one BID of Patient and Second BID of Donor

  Patient	Group	Donor	Group
  Akhter	A	Hussain	B

  CREATE TABLE #Group(BID INT,BName VARCHAR(50))
  CREATE TABLE #Patient(PID INT,PName VARCHAR(50),BID int)   
  CREATE TABLE #Donor(DID INT,DName VARCHAR(50),BID int)
  CREATE TABLE #CrossMatch(CID INT,PID int,DID int )   
    
     
  INSERT INTO #Group VALUES(1,'A')    
  INSERT INTO #Group VALUES(2,'B')    
  INSERT INTO #Patient VALUES(1,'Akhter','1')    
  INSERT INTO #Donor VALUES(1,'Hussain','2')   
  INsert into #CrossMatch Values(101,1,1)

• • Reply
  Yongqing Yu

  Contributor

  3710 Points

  1043 Posts

  Re: Two Foreign key from same table ,select query require?

  Feb 05, 2020 02:54 AM|Yongqing Yu|LINK

  Hi akhterr,

  To get the output, you need to divide into two parts to get the two groups of Patient, Group and Donor, Group respectively by using left join, and finally stitch them together by cross join.

  select * from
  (select p.PName as [Patient],g.BName as [Group]
   from #CrossMatch c left join 
  #Patient p on c.PID = p.PID
  left join #Group g on p.BID = g.BID) a
  cross join 
  (
  select d.DName as [Donor], g.BName as [Group]
   from #CrossMatch c  
  left join #Donor d on c.DID  =d.DID
  left join #Group g on d.BID = g.BID
  ) b

  Best Regards,

  YongQing.

  MSDN Community Support
  
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.