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

Question

Post reply

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

kramaswamy

SSCoach

Points: 18181
More actions
June 21, 2018 at 6:45 am

#406755

Hi all,
I often have situations where I have a table which stores, in two separate columns, a value and a source table. IE:
CREATE TABLE #Values
(
ID INT IDENTITY PRIMARY KEY,
ItemID INT,
TableName VARCHAR(200)
)
CREATE TABLE #Table1
(
ID INT IDENTITY PRIMARY KEY,
[Value] VARCHAR(MAX)
)
CREATE TABLE #Table2
(
ID INT IDENTITY PRIMARY KEY,
[Value] VARCHAR(MAX)
)
CREATE TABLE #Table3
(
ID INT IDENTITY PRIMARY KEY,
[Value] VARCHAR(MAX)
)
INSERT INTO #Values (ItemID, TableName)
VALUES (5, 'Table1'), (2, 'Table2'), (2, 'Table3'), (4, 'Table1')
INSERT INTO #Table1 ([Value])
VALUES ('Table1Value1'), ('Table1Value2'), ('Table1Value3'), ('Table1Value4'), ('Table1Value5')
INSERT INTO #Table2 ([Value])
VALUES ('Table2Value1'), ('Table2Value2'), ('Table2Value3'), ('Table2Value4'), ('Table2Value5')
INSERT INTO #Table3 ([Value])
VALUES ('Table3Value1'), ('Table3Value2'), ('Table3Value3'), ('Table3Value4'), ('Table3Value5')
Is there any better way of getting the "effective" value from the #Values table, other than:
SELECT
ID,
ItemID,
TableName,
[Value]
FROM #Values
CROSS APPLY
(
SELECT
  [Value]
FROM #Table1
WHERE ID = ItemID
  AND TableName = 'Table1'
UNION
SELECT
  [Value]
FROM #Table2
WHERE ID = ItemID
  AND TableName = 'Table2'
UNION SELECT
  [Value]
FROM #Table3
WHERE ID = ItemID
  AND TableName = 'Table3'
) c
PS - I spent like, 15 minutes trying to get this thing to format properly, but just could not figure it out. Any suggestions?

Viewing 15 posts - 1 through 15 (of 17 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

Ideally, you need to change your data set up. Is that something you're open to?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

kramaswamy SSCoach Points: 18181 More actions · Answer 2

Thom A - Thursday, June 21, 2018 6:50 AM
Ideally, you need to change your data set up. Is that something you're open to?

Can you expand on that thought, Thom?

I won't say that I'm open to anything, but the system isn't in production yet, so flexibility is pretty wide.

I did consider one possibility, in that rather than storing the table name, I could store the table object ID, and then do a join against sys.tables or something like that, but it seemed like overkill, and also potentially risky if the object IDs ever changed somehow

Thom A SSC Guru Points: 99126 More actions · Answer 3

kramaswamy - Thursday, June 21, 2018 6:57 AM
Thom A - Thursday, June 21, 2018 6:50 AM
Ideally, you need to change your data set up. Is that something you're open to?
Can you expand on that thought, Thom?
I won't say that I'm open to anything, but the system isn't in production yet, so flexibility is pretty wide.
I did consider one possibility, in that rather than storing the table name, I could store the table object ID, and then do a join against sys.tables or something like that, but it seemed like overkill, and also potentially risky if the object IDs ever changed somehow

It might be easier to show with some representative data. The problem you have at the moment, if that the method you have isn't going scale well (if you add a new table, you have to update all your queries). If you're setting up your relationships correctly, then there should be no need to have a set up like you have.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

kramaswamy - Thursday, June 21, 2018 6:57 AM
Thom A - Thursday, June 21, 2018 6:50 AM
Ideally, you need to change your data set up. Is that something you're open to?
Can you expand on that thought, Thom?
I won't say that I'm open to anything, but the system isn't in production yet, so flexibility is pretty wide.
I did consider one possibility, in that rather than storing the table name, I could store the table object ID, and then do a join against sys.tables or something like that, but it seemed like overkill, and also potentially risky if the object IDs ever changed somehow

This sounds like a really bad design to me. You are going to be forced to use dynamic constantly. It sounds like you have taken the anti-pattern of EAV (entity attribute value) to the next level of crazy. When you start storing the table to find data in a table it is a big red flag that something has gone horribly wrong in design.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kramaswamy SSCoach Points: 18181 More actions · Answer 5

I'll try to describe a scenario, though I'm not able to give you the exact structure of the tables / data.

Say I have three objects - Person, Seat, and Position.

I have a fourth object called Group, for which there are a whole slew of things that can be submitted to. For now, let's just talk about two - Transactions and Files.

I now need to set up some sort of relationship, that will allow me to connect these objects together. Essentially, I need to be able to say, for each of those Transactions and Files that are submitted to a Group, which of the Person, Seat, and Position records have access to those Transactions and Files.

So, to put this whole relationship into context, you could use the following structure:

CREATE TABLE #Group (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(200))
CREATE TABLE #Person (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(200))
CREATE TABLE #Seat (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(200))
CREATE TABLE #Position (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(200))
CREATE TABLE #Transaction (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(200))
CREATE TABLE #File (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(200))

INSERT INTO #Group ([Name]) VALUES ('Group 1'), ('Group 2')
INSERT INTO #Person ([Name]) VALUES ('Person 1'), ('Person 2')
INSERT INTO #Seat ([Name]) VALUES ('Seat 1'), ('Seat 2')
INSERT INTO #Position ([Name]) VALUES ('Position 1'), ('Position 2')
INSERT INTO #Transaction ([Name]) VALUES ('Transaction 1'), ('Transaction 2')
INSERT INTO #File ([Name]) VALUES ('File 1'), ('File 2')

CREATE TABLE #ItemType (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(200))
INSERT INTO #ItemType ([Name]) VALUES ('Group'), ('Person'), ('Seat'), ('Position'), ('Transaction'), ('File')

CREATE TABLE #GroupPersonItem (ID INT IDENTITY PRIMARY KEY, GroupID INT, PersonID INT, ItemID INT, ItemTypeID INT)
INSERT INTO #GroupPersonItem (GroupID, PersonID, ItemID, ItemTypeID) VALUES (1, 1, 1, 5), (1, 2, 2, 5)

SELECT * FROM #GroupPersonItem
OUTER APPLY
(
SELECT #Transaction.[Name] FROM #Transaction JOIN #ItemType ON #ItemType.ID = #GroupPersonItem.ItemTypeID AND #ItemType.[Name] = 'Transaction' WHERE #Transaction.ID = ItemID
UNION
SELECT #File.[Name] FROM #File JOIN #ItemType ON #ItemType.ID = #GroupPersonItem.ItemTypeID AND #ItemType.[Name] = 'File' WHERE #File.ID = ItemID
) o

DROP TABLE #ItemType
DROP TABLE #Group
DROP TABLE #Person
DROP TABLE #Seat
DROP TABLE #Position
DROP TABLE #Transaction
DROP TABLE #File
DROP TABLE #GroupPersonItem

Now - I know I could have a separate GroupPersonTransaction and GroupPersonFile table, and ditto for all the other relationships. However, that would require me to create a whole pile of extra procedures to access and update those tables. And at the end of the day, it doesn't really save me any extra work if I need to add a new table - rather than adding to the existing query, I would instead need to create the extra tables and procedures.

kramaswamy SSCoach Points: 18181 More actions · Answer 6

Sean Lange - Thursday, June 21, 2018 7:09 AM
kramaswamy - Thursday, June 21, 2018 6:57 AM
Thom A - Thursday, June 21, 2018 6:50 AM
Ideally, you need to change your data set up. Is that something you're open to?
Can you expand on that thought, Thom?
I won't say that I'm open to anything, but the system isn't in production yet, so flexibility is pretty wide.
I did consider one possibility, in that rather than storing the table name, I could store the table object ID, and then do a join against sys.tables or something like that, but it seemed like overkill, and also potentially risky if the object IDs ever changed somehow
This sounds like a really bad design to me. You are going to be forced to use dynamic constantly. It sounds like you have taken the anti-pattern of EAV (entity attribute value) to the next level of crazy. When you start storing the table to find data in a table it is a big red flag that something has gone horribly wrong in design.

I get what you're saying, Sean. Just bear in mind this is used in extremely limited context, and, in the context, I don't really see a better way of going about it. Though I'm of course open to suggestions.

The values being stored are only IDs (at most INT), and there are only a handful of records per lookup query, all of which is indexed.

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

kramaswamy - Thursday, June 21, 2018 7:27 AM
Sean Lange - Thursday, June 21, 2018 7:09 AM
kramaswamy - Thursday, June 21, 2018 6:57 AM
Thom A - Thursday, June 21, 2018 6:50 AM
Ideally, you need to change your data set up. Is that something you're open to?
Can you expand on that thought, Thom?
I won't say that I'm open to anything, but the system isn't in production yet, so flexibility is pretty wide.
I did consider one possibility, in that rather than storing the table name, I could store the table object ID, and then do a join against sys.tables or something like that, but it seemed like overkill, and also potentially risky if the object IDs ever changed somehow
This sounds like a really bad design to me. You are going to be forced to use dynamic constantly. It sounds like you have taken the anti-pattern of EAV (entity attribute value) to the next level of crazy. When you start storing the table to find data in a table it is a big red flag that something has gone horribly wrong in design.
I get what you're saying, Sean. Just bear in mind this is used in extremely limited context, and, in the context, I don't really see a better way of going about it. Though I'm of course open to suggestions.
The values being stored are only IDs (at most INT), and there are only a handful of records per lookup query, all of which is indexed.

I am missing something. In the sample ddl you posted NONE of it has the table and columns in the data like you stated in the original post.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kramaswamy SSCoach Points: 18181 More actions · Answer 8

Yeah the original example was a bit more quickly made, but the differences are pretty minor. Rather than having TableName stored in the #GroupLineItem table, I have ItemTypeID stored, with TableName being in the #Item table. Sure, there's a bit of a difference in the lookup for a VARCHAR column instead of an INT column pointing to another table storing the VARCHAR column, but I expect the difference in performance to be minimal, as both situations can be covered by indexes.

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

kramaswamy - Thursday, June 21, 2018 10:19 AM
Yeah the original example was a bit more quickly made, but the differences are pretty minor. Rather than having TableName stored in the #GroupLineItem table, I have ItemTypeID stored, with TableName being in the #Item table. Sure, there's a bit of a difference in the lookup for a VARCHAR column instead of an INT column pointing to another table storing the VARCHAR column, but I expect the difference in performance to be minimal, as both situations can be covered by indexes.

But ItemTypeID would be a foreign key, not the name of a table. Your original post would require dynamic sql for everything. Your new sample doesn't require dynamic sql. But then with the new example it is not at all clear what you are asking. Can you try to elaborate on what these relationships are that you are referring to?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kramaswamy SSCoach Points: 18181 More actions · Answer 10

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 11

This question just keeps changing with each iteration. I don't understand what you are asking. If the question is about your last query then I would say just use a join instead of cross apply with a UNION. But this is so confusing to me at this point my head is spinning.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sgmunson SSC Guru Points: 110639 More actions · Answer 12

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.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

kramaswamy SSCoach Points: 18181 More actions · Answer 13

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.

Sean Lange SSC Guru Points: 286573 More actions · Answer 14

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.

Well if you can't explain it then I can't help. I don't understand what your question even is at this point. Best of luck I truly hope you figure out a solution that works.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/