I'm sure someone has a better idea about this so here goes - I have a table with a bunch of ids (@tbLink) that represent rows in other tables. I'm trying to express here
declare @tbLink table (linkid int, identitytypeid int,
itemid int, categoryid int,parentid int)
declare @tbCat table (categoryid int, name varchar(20))
declare @tbId table (typeid int, typename varchar(20))
declare @tbDomain table (domainid int, domainname varchar(20))
--
declare @tbModule table (moduleid int, modulename varchar(20))
declare @tbProgram table (programid int, programname varchar(20))
INSERT INTO @tbLink VALUES (1, 1, 1, 1, 1)
INSERT INTO @tbLink VALUES (2, 1, 1, 2, 1)
INSERT INTO @tbCat VALUES (1, 'Program')
INSERT INTO @tbCat VALUES (2, 'Module')
INSERT INTO @tbId VALUES (1, 'Domain')
INSERT INTO @tbId VALUES (2, 'Group')
INSERT INTO @tbDomain VALUES (1, 'DEV')
INSERT INTO @tbModule VALUES (1, 'Module1')
INSERT INTO @tbProgram VALUES (2, 'ProgramA')
select t.*, i.typename, c.name, d.domainname, COALESCE(m.modulename, p.programname)
as objectname from @tbLink t
inner join
@tbId i on t.identitytypeid = i.typeid
inner join
@tbCat c on t.categoryid = c.categoryid
inner join
@tbDomain d on t.parentid = d.domainid
left join
@tbModule m on m.moduleid = t.itemid
left join
@tbProgram p on p.programid = t.itemid
My results are:
1 1 1 1 1 Domain Program DEV Module1
2 1 1 2 1 Domain Module DEV Module1
But I was expecting row 1 to be 'ProgramA' not 'Module1' - am I missing something here? Is this the proper use of COALESCE also?
Cheers
Mike
both rows have a item ID of 1.
i think you may want to join them on the category id AND the item id., change ProgramA to have a programid of 1, and in your join:
left join
@tbModule m on t.categoryid = 2 AND m.moduleid = t.itemid
left join
@tbProgram p on t.categoryid = 1 AND p.programid = t.itemid