locked
Two columns need cross reference to the same column in another table RRS feed

  • Question

  • Greetings,

       I have two tables - one has references to GUIDs in two columns something like this:

    TABLEREQ

    Requestref    Acctref   Created

    ------------    --------   ---------

    GUID1          GUID2    12-12-19

    GUID3          GUID2    12-12-19

    Script           GUID5    12-12-19

    The other table is a cross reference from GUIDs to names:

    TABLEGUIDS

    GUID         Name

    -------        --------

    GUID1       John

    GUID2       Mary

    GUID3       Peter

    GUID5       David

    What Im after is a select statement that will replace the GUIDs with names and to make it tricky, if the RequestRef is not in the second table then it must be reported asis -like this:

    RequestName    AcctName   Created

    John                 Mary           12-12-19

    Peter                Mary           12-12-19

    Script               David          12-12-19

    Is this do-able in T-SQL?

    Thanks

    David Z

    Tuesday, December 17, 2019 3:34 AM

Answers

  • I may be using coalesce instead of isnull to avoid truncation of data.

    select  
    coalesce(t1.Name,r.Requestref) RequestName
    , t2.name AcctName
    , r.Created  
    from TABLEREQ r 
    left join TABLEGUIDS t1 on r.Requestref=t1.GUID
    left join TABLEGUIDS t2 on r.Acctref=t2.GUID
    


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    • Marked as answer by David Zemdegs Tuesday, December 17, 2019 8:20 PM
    Tuesday, December 17, 2019 4:31 AM

All replies

  • CREATE TABLE TABLEREQ(
       Requestref VARCHAR(6) NOT NULL 
      ,Acctref    VARCHAR(5) NOT NULL
      ,Created    DATE  NOT NULL
    );
    INSERT INTO TABLEREQ(Requestref,Acctref,Created) VALUES
     ('GUID1','GUID2','12-12-19')
    ,('GUID3','GUID2','12-12-19')
    ,('Script','GUID5','12-12-19');
    
    
    CREATE TABLE TABLEGUIDS(
       GUID VARCHAR(5) NOT NULL 
      ,Name VARCHAR(5) NOT NULL
    );
    INSERT INTO TABLEGUIDS(GUID,Name) VALUES
     ('GUID1','John')
    ,('GUID2','Mary')
    ,('GUID3','Peter')
    ,('GUID5','David');
    select  
    isnull(t1.Name,r.Requestref) RequestName
    , t2.name AcctName
    , r.Created  
    from TABLEREQ r 
    left join TABLEGUIDS t1 on r.Requestref=t1.GUID
    left join TABLEGUIDS t2 on r.Acctref=t2.GUID
    
    drop table TABLEREQ,TABLEGUIDS

    Tuesday, December 17, 2019 3:43 AM
  • I may be using coalesce instead of isnull to avoid truncation of data.

    select  
    coalesce(t1.Name,r.Requestref) RequestName
    , t2.name AcctName
    , r.Created  
    from TABLEREQ r 
    left join TABLEGUIDS t1 on r.Requestref=t1.GUID
    left join TABLEGUIDS t2 on r.Acctref=t2.GUID
    


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    • Marked as answer by David Zemdegs Tuesday, December 17, 2019 8:20 PM
    Tuesday, December 17, 2019 4:31 AM
  • CREATE TABLE #TABLEREQ(
       Requestref VARCHAR(6) NOT NULL 
      ,Acctref    VARCHAR(5) NOT NULL
      ,Created    DATE  NOT NULL
    );
    INSERT INTO #TABLEREQ(Requestref,Acctref,Created) VALUES
     ('GUID1','GUID2','12-12-19')
    ,('GUID3','GUID2','12-12-19')
    ,('Script','GUID5','12-12-19');
    CREATE TABLE #TABLEGUIDS(
       GUID VARCHAR(5) NOT NULL 
      ,Name VARCHAR(5) NOT NULL
    );
    INSERT INTO #TABLEGUIDS(GUID,Name) VALUES
     ('GUID1','John')
    ,('GUID2','Mary')
    ,('GUID3','Peter')
    ,('GUID5','David');


    select RequestName=isnull((select top 1 name from #TABLEGUIDS b where a.Requestref = b.GUID),a.Requestref),
     AcctName=isnull((select top 1 name from #TABLEGUIDS b where a.Acctref = b.GUID),a.Acctref),
    created
    from #TABLEREQ a
    Tuesday, December 17, 2019 5:21 AM
  • Hi David

    Also , please try to use CASE WHEN to achieve your requirement .

    CREATE TABLE TABLEREQ(
       Requestref VARCHAR(6) NOT NULL 
      ,Acctref    VARCHAR(5) NOT NULL
      ,Created    DATE  NOT NULL
    );
    INSERT INTO TABLEREQ(Requestref,Acctref,Created) VALUES
     ('GUID1','GUID2','12-12-19')
    ,('GUID3','GUID2','12-12-19')
    ,('Script','GUID5','12-12-19');
    
    
    CREATE TABLE TABLEGUIDS(
       GUID VARCHAR(5) NOT NULL 
      ,Name VARCHAR(5) NOT NULL
    );
    INSERT INTO TABLEGUIDS(GUID,Name) VALUES
     ('GUID1','John')
    ,('GUID2','Mary')
    ,('GUID3','Peter')
    ,('GUID5','David');
    
    select  
    case when t1.Name is null then r.Requestref else t1.Name end  RequestName,
    case when t2.Name is null then r.Acctref else t2.Name end  AcctName,
     r.Created  
    from TABLEREQ r 
    left join TABLEGUIDS t1 on r.Requestref=t1.GUID
    left join TABLEGUIDS t2 on r.Acctref=t2.GUID
    /*
    RequestName AcctName Created
    ----------- -------- ----------
    John        Mary     2019-12-12
    Peter       Mary     2019-12-12
    Script      David    2019-12-12
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 17, 2019 6:54 AM
  • If these GUIDx represent real unique identifiers, then check this example too:

    declare @TABLEREQ as table 
    (
        Requestref varchar(50),
        Acctref varchar(50),
        Created date
    )
    
    insert into @TABLEREQ values
     ( '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000002', '12-12-19'),
     ( '00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000002', '12-12-19'),
     ( 'Script', '00000000-0000-0000-0000-000000000005', '12-12-19')
    
    declare @TABLEGUIDS as table
    (
        [GUID] uniqueidentifier,
        [Name] varchar(50)
    )
    
    insert into @TABLEGUIDS values
     ( '00000000-0000-0000-0000-000000000001', 'John'),
     ( '00000000-0000-0000-0000-000000000002', 'Mary'),
     ( '00000000-0000-0000-0000-000000000003', 'Petter'),
     ( '00000000-0000-0000-0000-000000000005', 'David')
    
    
    select * from @TABLEREQ
    
    update t
    set Requestref = isnull((select [Name] from @TABLEGUIDS where [GUID] = TRY_CAST(t.Requestref as uniqueidentifier)), Requestref),
          Acctref      = isnull((select [Name] from @TABLEGUIDS where [GUID] = TRY_CAST(t.Acctref as uniqueidentifier)), Acctref)
    from @TABLEREQ as t
    
    select * from @TABLEREQ
    

    • Edited by Viorel_MVP Tuesday, December 17, 2019 7:27 AM
    Tuesday, December 17, 2019 7:24 AM
  • This was really awesome bro..helped me 
    Tuesday, December 17, 2019 7:29 AM