I'm not still not totally following what you mean. Either way, the table name is being used:
SELECT #Transaction.[Name]
FROM #Transaction
JOIN #ItemType ON #ItemType.ID = #GroupPersonItem.ItemTypeID AND #ItemType.[Name] = 'Transaction'
WHERE #Transaction.ID = ItemID
Versus the original example, which, in the context of my second example, would have been:
SELECT #Transaction.[Name]
FROM #Transaction
WHERE #Transaction.ID = ItemID AND #GroupPersonItem.TableName = 'Transaction'
Or, to put another way, I could have re-written the original example as:
SELECT [Value]
FROM #Table1
JOIN #ItemType ON #ItemType.ID = #Values.ItemTypeID AND #ItemType.Name = 'Transaction'
WHERE #Table1.ID = #Values.ItemID.
As for the second part of your question, let me try phrasing what I'm trying to accomplish a bit differently:
For each type of Item that is associated to a Group, I want to restrict which Person/Seat/Position is able to access that Item. As far as I can tell, I can do that in a few ways. I can have:
A) A separate Group[IndividualType][ItemType] table for each - IE GroupPersonTransaction, GroupPersonFile, GroupSeatTransaction, GroupSeatFile, etc... and within this table, for example, in GroupPersonFile, have have GroupID, PersonID, and FileID
B) A single Group[IndividualType]Item table for each - IE GroupPersonItem, GroupPersonFile, etc... and within this table, for example, in GroupPersonItem, have GroupID, PersonID, ItemID, and ItemTypeID
C) A single GroupIndividualType[ItemType] table for each - IE GroupIndividualTypeTransaction, GroupIndividualTypeFile, etc... and within this table, for example, GroupIndividualTypeFile, have GroupID, ItemID, ItemTypeID, and FileID
D) A single GroupIndividualTypeItemType table, and within this table, have GroupID, IndividualID, IndividualTypeID, ItemID, ItemTypeID
In my specific case, I went with B, because it best fit my particular use case. And in that particular case, what I was trying to ask in this thread, is whether there was a better way of writing a query to get information about the specific Item, other than doing a UNION with a lookup:
SELECT #GroupPersonItem.*, #ItemName
FROM #GroupPersonItem
JOIN #ItemType ON #ItemType.ID = #GroupPersonItem.ItemTypeID
CROSS APPLY
(
SELECT #Transaction.[Name] AS [ItemName]
FROM #Transaction
WHERE #Transaction.ID = ItemID AND #ItemType.[Name] = 'Transaction'
UNION
SELECT #File.[Name] AS [ItemName]
FROM #File
WHERE #File.ID = ItemID AND #ItemType.[Name] = 'File'
) c