locked
Two Foreign key from same table ,select query require? RRS feed

  • Question

  • User-367318540 posted

    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)

    Tuesday, February 4, 2020 5:46 AM

Answers

  • User665608656 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 5, 2020 2:54 AM