none
T-SQL intermediate/lookup table join

    Question

  • I have a lookup/intermadiate table that contains the following fields:

    group_objectguid, membergroup_objectguid

    I need to translate group_objectguid and membergroup_objectguid to a samaccountname from a table called groups

    the groups table contains the fields : objectguid, samaccountname.

    Iam stuck at a join on :

    Select b.samaccountname from groupmembers as a inner join groups as b on b.objectguid = a.group_objectguid

    So I can translate the group_objectguid to as samaccountname. But how to proceed to get the membergroup_objectguid  to the samaccountname?


    With Kind Regards, Léon


    • Edited by Léon W Tuesday, August 20, 2013 9:47 AM
    Tuesday, August 20, 2013 8:26 AM

Answers

  • Not sure, what you are looking for. But you may try below:

    If this is not correct, you may provide tablestructure and sample data along with your desired result. We would be able to help you better.

    declare @groupmembers table(group_objectguid int, membergroup_objectguid int)
    insert into @groupmembers values(1,11)
    insert into @groupmembers values(1,22)
    insert into @groupmembers values(2,11)
    insert into @groupmembers values(2,22)
    -------------------
    declare @groups table( objectguid int, samaccountname varchar(10))
    insert into @groups values(1,'xyz')
    insert into @groups values(2,'abc')
    insert into @groups values(11,'memberxyz')
    insert into @groups values(22,'memberabc')
    ---------------------
    Select a.group_objectguid,membergroup_objectguid
    ,b.samaccountname 'GroupSamname',c.samaccountname 'memberGroupSamname'
    from @groupmembers a 
    Left join @groups b on b.objectguid = a.group_objectguid
    Left join @groups c on c.objectguid = a.membergroup_objectguid


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Léon W Tuesday, August 20, 2013 9:58 AM
    Tuesday, August 20, 2013 8:43 AM

All replies

  • Are you looking for the below query,

    declare @groupmembers table(group_objectguid int, membergroup_objectguid int)
    insert into @groupmembers values(1,11)
    insert into @groupmembers values(1,22)
    insert into @groupmembers values(2,11)
    insert into @groupmembers values(2,22)
    -------------------
    declare @groups table( objectguid int, samaccountname varchar(10))
    insert into @groups values(1,'xyz')
    insert into @groups values(2,'abc')
    ---------------------
    Select a.group_objectguid,membergroup_objectguid,b.samaccountname from @groupmembers a inner join @groups b 
    on b.objectguid = a.group_objectguid


    Regards, RSingh

    Tuesday, August 20, 2013 8:35 AM
  • Not sure, what you are looking for. But you may try below:

    If this is not correct, you may provide tablestructure and sample data along with your desired result. We would be able to help you better.

    declare @groupmembers table(group_objectguid int, membergroup_objectguid int)
    insert into @groupmembers values(1,11)
    insert into @groupmembers values(1,22)
    insert into @groupmembers values(2,11)
    insert into @groupmembers values(2,22)
    -------------------
    declare @groups table( objectguid int, samaccountname varchar(10))
    insert into @groups values(1,'xyz')
    insert into @groups values(2,'abc')
    insert into @groups values(11,'memberxyz')
    insert into @groups values(22,'memberabc')
    ---------------------
    Select a.group_objectguid,membergroup_objectguid
    ,b.samaccountname 'GroupSamname',c.samaccountname 'memberGroupSamname'
    from @groupmembers a 
    Left join @groups b on b.objectguid = a.group_objectguid
    Left join @groups c on c.objectguid = a.membergroup_objectguid


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Léon W Tuesday, August 20, 2013 9:58 AM
    Tuesday, August 20, 2013 8:43 AM
  • @Latheesh: Your solution was spot on. I missed the logic of the join and used te wrong join type.

    Thank you!


    With Kind Regards, Léon

    Tuesday, August 20, 2013 9:59 AM