Any better way of querying multiple tables based on a column value?

  • kramaswamy - Thursday, June 21, 2018 8:01 PM

    sgmunson - Thursday, June 21, 2018 3:48 PM

    Yep, I have to agree with Sean.   This whole thing is a head-spinner.   How about a 30,000 foot overview of what, in the REAL world, the data is actually going to represent.   Don't assume that anyone knows ANYTHING about your business or it's objectives.   Then state what you are trying to accomplish by storing the data, and what kind of output you are going to need.   Explain the WHY, and that might help us get you better answers.

    I'm really not sure what more I can explain. My latest post is literally exactly the problem I'm trying to solve. The table design and specifications are as close to the actual real-world ones as I can get. If you have any suggestions on how I can clarify the problem better, please let me know :/

    And I suppose I should make one thing clear - I'm not dealing with a problem for which I have no solution. I have a solution, and I'm just asking if there is a better approach than the one I've taken.

    To abstractify the situation a bit, the scenario is that I have a One-to-Many-to-Many relationship for which I need to design tables to store data and queries to access. The design I chose is to represent this instead as a One-to-Many-to-One relationship, where the latter "one" is represented by the ItemTypeID-ItemID concept.

    Okay, but ask yourself this question...  We clearly don't quite understand your situation, so which of the following two reasons do you think that is?  Is it that we're time-constrained volunteers and can't spend an hour or two digging deep into your monster code and coming to the same conclusion as you have?    Or is that you've utterly failed to provide a clear explanation of what your actual real world entities are, in language that doesn't involve ANY vague statements?   Or maybe a little of both ???    Seriously,  dude... you're asking us to see what you see with only some sizable code to review and very vague descriptions that just aren't all that useful.   Ask a better question, get a better answer.   I'm with Sean.   When you "Splain, Lucy", we'll be glad to help.   Until then...  not so much...

  • Steve I'm not trying to criticize either you or Sean. I get your points, and I get that my explanation is not clear. But I'm just not sure how to clarify it any better. I've tried re-stating the problem in multiple different ways, without success.

    Maybe a picture?

    My goal is to figure out the best structure for that box TBD in the middle, and then to find the best way of writing queries to access the data contained within.

    The design I concluded was best used is the following:

    And a query to access the data, to answer the following question:

    "For each Group, give me all of the Transaction and File records that People can access, and for each, give me the Name property"

    Based on that structure, was:

    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

  • kramaswamy - Friday, June 22, 2018 1:44 PM

    Steve I'm not trying to criticize either you or Sean. I get your points, and I get that my explanation is not clear. But I'm just not sure how to clarify it any better. I've tried re-stating the problem in multiple different ways, without success.

    Maybe a picture?

    My goal is to figure out the best structure for that box TBD in the middle, and then to find the best way of writing queries to access the data contained within.

    The design I concluded was best used is the following:

    And a query to access the data, to answer the following question:

    "For each Group, give me all of the Transaction and File records that People can access, and for each, give me the Name property"

    Based on that structure, was:

    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

    Well, it's better enough that I can start asking better questions...  it's still not great.   Honestly, I think you're just not being sufficiently thorough, and hoping that  the easy way out works.   If you were to alias your tables in your query, that would make it much more clear as to how this works.   What you appear to have is an entity relationship table that maps the GroupPersonItem table through an ItemID to a combination of the transaction and file tables.  It also does not appear that the seat table or the GroupSeatItem table have any importance to the task at hand.   From a design perspective, it's a rather standard design, not terribly different than some of the kinds of relationships you might find in a data warehouse.   Cross reference tables are very common in a DW.   As there's been nothing provided that indicates how this system fits into anything else at your company, or what kind of row counts these tables will acquire, there's not much in the way of specifics to suggest another alternative.   Do try to recall that design usually 100% relies on intimate knowledge of both the problem at hand as well as the environment into which it must fit.  As we have relatively little of the former, and none of the latter, your expectations should be that you get very generalized advice....

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply