SQL Table Join when the name of a column in Table1 equals the value of a column in Table2

5 pts.
Tags:
Conditional SQL statements
Conditional statements
DB2 SQL
JOIN statement
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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 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 more information, and some example data including how the resulting join table would look like.
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following