Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GVeers
Starting Member
9 Posts |
 Posted - 2011-02-18 : 09:52:35
|
| Hello,I have two tables that have an obvious relationship with each other if you were trying to explain it to a person. But translating that relationship into a SQL join has been tricky.Table 1FIELD1 | FIELD2 | ... | FIELD10 | FIELD11Data | Data | ... | Value1 | Value1Data | Data | ... | Value2 | Value1etc.Table 2NAME | DESCRIPTION | CODE | TBNAMEFIELD10 | InterestingInfo | Value1 | Table1FIELD10 | OtherGoodInfo | Value2 | Table1FIELD11 | DifferentGoodInfo | Value1 | Table1FIELD11 | StillGoodInfo | Value2 | Table1So basically Table 2 has some useful data, but its format is flipped from Table 1. In other words field names in Table 1 are values in Table 2 (the "NAME" field in Table 2). And, there are multiple possibilities for each field, so that I may need to pull a different row depending on the field's value in Table 1. So how can I do some kind of join where the joining information is a column in one table and a value in the other? It seems like I'd need a vlookup-type functionality... |
|
|
chris_n_osborne
Starting Member
34 Posts |
Posted - 2011-02-18 : 19:23:39
|
| SQL is meant to join columns. I do not think you can join the values in a column in Table 2 to the multiple columns in Table 1.To me, Table 1 looks like it was created using a PIVOT based on Table 2. I say that because Table 1 does not look like any kind of table you would want to see in a database (repeating columns are considered bad design). Are you sure Table 1 isn't a VIEW based on Table 2?If Table 1 is a permanent table in your database, it should have a fixed number of columns, and I think you may want to think about using UNPIVOT on Table 1 (basically the reverse of a PIVOT/crosstab):http://msdn.microsoft.com/en-us/library/ms177410.aspxOnce you had a query generating an UNPIVOT, you could put it in a common table expression (CTE) and join it to Table 2.If Table 1 has a variable number of FIELD1 to FIELDxx columns, then it is not a permanent table in your database, and is based on Table 2 through a VIEW (or CTE, or similar abstraction). If so, you may want to review your requirement to join Table 2 to Table 1: you would probably be able to get whatever you wanted just from Table 2. |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-02-18 : 22:05:41
|
| You have no understanding at all of RDBMS. You don't even know that fields are not columns. You confuse names and values, etc. If I used an example this bad in one of my books, people would think I made it up. Start off with the simplest intro book I know -- http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Joining column names to row values