Join 2 tables on column name = field value

Question

Join 2 tables on column name = field value

sql-532751

SSC Rookie

Points: 36
More actions
October 29, 2007 at 7:13 am

#153921

Hi,
I couldn't see an answer to this question so apologies if it is already posted.
Is it possible to join 2 tables where the join is made depending on a value in a field. For example, table 1 has 2 columns called Manual and Datasheet. Table 2 has a column called ProductType.
I need to join Table 1 and Table 2 and would like to say:
FROM Table1 INNER JOIN Table2 ON Table1.(insert value from Table2.ProductType) = 'xxx'
This would essentially translate into
FROM Table1 INNER JOIN Table2 ON Table1.Datasheet = 'xxx'
or
FROM Table1 INNER JOIN Table2 ON Table1.Manual = 'xxx'
depending on whether the value in Table2.ProductType was 'Manual' or 'Datasheet'. So Table2.ProductType holds the name of the column to be used in the join.
Is that possible?
Thanks,
Paul

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Yes it is possible..

FROM Table1 INNER JOIN Table2 ON ( CASE Table2.ProductType WHEN 'Datasheet' THEN Table1.Datasheet WHEN 'Manual' THEN Table1.Manual ELSE 'Unknown' END ) = 'xxx'

--Ramesh

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

...except that you haven't defined an actual join criteria. This is a full CROSS join, or cartesian product as it's called. Every row of table1 is joined to every row in table2.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sql-532751 SSC Rookie Points: 36 More actions · Answer 3

Thanks for the quick reply. That's useful to know - never used CASE before in SQL.

What I was ideally trying to do was lookup the value in the ProductType field and convert that to the column name to be joined on. In the example, I mentioned Datasheet and Manual but it could be a number of different values, so I was trying to avoid manually doing Ifs or Cases.

Sorry if I'm being confusing or unclear

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Could you help us by providing sample script with data and the output that you want?

--Ramesh

Marshall Smith SSCrazy Points: 2041 More actions · Answer 5

This sounds very much like you are running into a problem with improperly normalized data. If at all possible, I suggest re-designing the table in question. Rather than one column for datasheet, a second column for manual, etc., have a ProductType column and a ProductValue column. Then, the join is pretty obvious.

If that is not possible, you may have an issue. Most of the solutions I can think of rely on CASE, IF..THEN structures, or UNION. Generalizing these for any column input is going to be tricky. There will also be the issue that the resulting query will, necessarily, not be optimized. You could see some definite performance hits.

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 6

Try this:

from table1 inner join table2 on table1.col1 = table2.col1

where table1.ProductType ='Datasheet'

union

from table1 inner join table2 on table1.col1 = table2.col2

where table1.ProductType ='Manual'

The net result is that the first join will occur for all Datasheet items and the second join will occur for all Manual items. Both selects need to have the same column list returned for this to work.

HTH