Home Forums SQL Server 2008 T-SQL (SS2K8) Any better way of querying multiple tables based on a column value? RE: Any better way of querying multiple tables based on a column value?

  • 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