Any better way of querying multiple tables based on a column value?
- kramaswamy - Thursday, June 21, 2018 8:01 PM
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 (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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 PMSteve 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'
) cWell, 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....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply