SQL Table Join when the name of a column in Table1 equals the value of a column in Table2
Home > IT Answers > Development > SQL Table Join when the name of a column in T...
Bchat
5 pts.
0
Q:
SQL Table Join when the name of a column in Table1 equals the value of a column in Table2
SQL, JOIN statement, Conditional statements, DB2 SQL, Conditional SQL statements, Conditional JOIN
Does anyone know how to write the SQL to join two DB2 tables when the name of Table1.columnA equals the data values stored in Table2.columnB?

In the table structure examples listed below, I'm trying to figure out how to join Table1 and Table2 when the values in Table2.Tag_Name are equal to the column names in Table1, i.e. Public_Housing, Private_Funding, Public_Education

Table1.
Account_Id
Status_Id
Public_Housing
Private_Funding
Public_Education

Table2.
Group_Id
Tag_Id
Tag_Name

Thanks.
ASKED: Dec 4 2008  8:13 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
BronxBrent
15 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
I had the same question and could not easily find any answers.

Here is what I came up with.
The sysobjects table stores each table name with a type = 'u'
The syscolumns table stores the column name for each table.
Each of these tables shares an ID column, which you can use to join on.
This will allow you to join on any of your data tables and display the name of that table and the name of any of those table columns.
When you join on your second data table, you then specify which column name from your data table 1 equals the value from a column in your data table 2.

You will want to customize the select statement and enter your own table names, but the syntax looks something like this:

select so.name TableName, sc.name ColumnName, t1.*, t2.*
from [DBName].dbo.sysobjects so
join [DBName].dbo.syscolumns sc on so.id = sc.id and so.type = 'u'
join [DBName].dbo.[table1] t1 on so.name = '[table1]'
join [DBName].dbo.[table2] t2 on t2.[columnName] = sc.name and sc.name = '[ColumnName]'

Note: table 1 is the table that has the column name that exists as a value in table 2.

Hopefully this makes sense.
Last Answered: Nov 3 2009  0:20 AM GMT by BronxBrent   15 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29845 pts.  |   Dec 4 2008  2:41PM GMT

Are Table1’s fields going to be created/added at runtime ? If not, I don’t really understand how that ‘join’ would work since you have in Table1 all three fields, and you know exactly their names, so you could compare Table2.tag_name directly against those field names (as strings). At the other hand, all rows in Table1 will have the same fields, so if one record in Table2 has ‘Public_Education’ in Tag_name, that record will match the complete Table1 table, since all records on that table has the same fields.

I might have misunderstood your question. If so, please <a href="phttp://itknowledgeexchange.techtarget.com/itanswers/sql-table-join-when-the-name-of-a-column-in-table1-equals-the-value-of-a-column-in-table2#rovide" title="phttp://itknowledgeexchange.techtarget.com/itanswers/sql-table-join-when-the-name-of-a-column-in-table1-equals-the-value-of-a-column-in-table2#rovide" target="_blank">phttp://itknowledgeexchange.techtarget.c…</a> more information, and some example data including how the resulting join table would look like.

 
0