0

I have two tables as below that I am looking to join dynamically using the ColVal in TMaster with ColName in TMaster which is the name of the column to match with in TTable1.

TMaster:

enter image description here

TTable1:

enter image description here

So, final result would be the data from TTable1 that matches the colName and ColVal in TMaster. How do I achieve this?

select tt.Name 
from TMaster tm 
join TTable1 tt on TMaster.ColVal = tt.[ColName from TMaster should be used here]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TSR
  • 714
  • 2
  • 7
  • 24
  • 4
    Fix your design, that's the real solution. Going down this path break basic referencial integrity principles, and will be both a nightmare to maintain and work with. If the design are using is mandating that solutions you implement must be dynamic, then the design is flawed. – Thom A Jul 11 '21 at 15:06
  • Do you know the names of all the possible columns upfront? And what do you propose should be the result if there are `Name` filters also, should all results also be in one of those `Name` filters, or is it enough that it matches *either* `ID` *or* `Name`? – Charlieface Jul 11 '21 at 15:55
  • @Charlieface No, we do not know the possible columns upfront. Just one of the matches is good enough, i.e. either ID or Name – TSR Jul 11 '21 at 16:41
  • @Larnu Not my design, this design is fixed and given to me, I have to consume this now with this requirement. – TSR Jul 11 '21 at 16:43
  • So you don't know *anything* at all about `TTable1` before this is executed, you don't know what any of the columns are? And what do you do about multiple filters? – Charlieface Jul 11 '21 at 20:52
  • I do not know what could be value of ColName is TMaster, but I am certain that whatever the ColName in there, it would certainly exist as a Column in TTable1. No multiple filters. – TSR Jul 12 '21 at 03:46
  • 1
    If I'm understanding this correctly, a SQL statement would have to be generated for every row of TMaster since the join criteria could be different for every row. Refactoring that a bit, one could group up TMaster rows with the same ColName and then generate a single SQL statement for each group. At any rate an iterative and dynamically-generated-sql approach is going to be needed and won't be possible with straight SQL (short of a cross-join and some prayers). You'll have to script this out in a stored procedure or non-sql language like Python (as an example). – JNevill Jul 14 '21 at 18:26
  • you could potentially opt for [dynamic sql](https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-dynamic-sql/) but this looks like a very slippery slope – timur Jul 15 '21 at 00:22
  • I don't understand: how can you *not* know what *possible* columns are in `TTable1`, surely you must have the table definition. Let's have a list of all the columns in `TTable1`, then it becomes far simpler – Charlieface Jul 15 '21 at 20:00
  • What is the datatype of `TMaster.ColVal`? Is it possible that you JOIN with mixed datatypes e.g. ID = 1 in first row and Name = 3rd value in second row? – Salman A Jul 16 '21 at 13:26
  • Your sample inputs are too tight to demonstrate the general case. How about more of the columns on `TTable1` and more values in both `TMaster` and `TTable1`. A sample output would be nice to go along with the more complex inputs. Also, how do you want to handle the case where a value in `TMaster.ColName` is not a column in `TTable1`? And it seems odd that you're being asked to write a query and don't have the table definition available. – dougp Jul 16 '21 at 22:37
  • If you go down this route I can guarantee you'll be stuck supporting this for a very long time and no other dev will thank you if you leave it to them! – Steve Ford Jul 18 '21 at 20:14

2 Answers2

1

Why not :

SELECT tt.Name 
FROM   TMaster AS tm
       JOIN TTable1 AS tt 
          ON tm.ColVal = CASE ColVal 
                             WHEN 1 THEN tt.Col1
                             WHEN 2 THEN tt.Col2
                             WHEN 3 THEN tt.Col3
                          END
SQLpro
  • 3,994
  • 1
  • 6
  • 14
0

You can formulate your query in the following manner

SELECT tt.Name 
FROM TMaster tm
JOIN TTable1 tt ON 
    tm.ColName = 'Col1' AND tm.ColVal = tt.Col1 OR
    tm.ColName = 'Col2' AND tm.ColVal = tt.Col2 OR
    tm.ColName = 'Col3' AND tm.ColVal = tt.Col3 OR
....

It's not going to be very performant though.

If you do not even know your column names (why that should be, I don't know) then you need dynamic SQL.

DECLARE @sql nvarchar(max) = '
SELECT tt.Name 
FROM TMaster tm
JOIN TTable1 tt ON 
' +
(
    SELECT STRING_AGG(CAST(
'    tm.ColName = ' + QUOTENAME(c.name, '''') + ' AND tm.ColVal = tt.' + QUOTENAME(c.name)
    AS nvarchar(max)), N' OR
'
    )
    FROM sys.columns
    WHERE OBJECT_NAME(object_id) = 'TTable1'
);

PRINT @sql; -- for testing

EXEC sp_executesql @sql;
Charlieface
  • 52,284
  • 6
  • 19
  • 43