ansaurus

Question

Joining multiple columns in one table to a single column in another table

Answer 1

A:

From updated question

Perhaps something like this? It will be messy no matter what you do.

SELECT S.ID
  S.TEXT1_1,
  TEXT1_1_RID = COALESCE((SELECT MAX(R.ID) FROM Reference R WHERE R.SCHAREACODE = S.TEXT1_1), 0),
  S.TEXT1_2,
  TEXT1_2_RID = COALESCE((SELECT MAX(R.ID) FROM Reference R WHERE R.SCHAREACODE = S.TEXT1_2), 0),
  ...
FROM Schedule S

beach 2009-04-20 20:40:14

I suspect this might be the only way.

Steve Syfuhs 2009-04-20 21:21:59

Answer 2

+3 A:

I think he means to join on the Reference table multiple times:

SELECT *
  FROM Schedule AS S
 INNER JOIN Reference AS R1 
         ON R1.ID = S.FirstID 
 INNER JOIN Reference AS R2 
         ON R2.ID = S.SecondID 
 INNER JOIN Reference AS R3 
         ON R3.ID = S.ThirdID 
 INNER JOIN Reference AS R4 
         ON R4.ID = S.ForthID

TheSoftwareJedi 2009-04-20 20:43:55

Answer 3

A:

Your description is a bit lacking, so I'm going to assume that

Schedule has 50+ columns (it's almost completely denormalized -- not my design), most of which contain a value that could be joined to a column in the Reference table.

means that 1 of the 50+ columns in Schedule is a ReferenceId. So, given a table design like:

Schedule ( MaybeReferenceId1, MaybeReferenceId2, MaybeReferenceId3, ... )
Reference ( ReferenceId )

Something like:

SELECT *
FROM Schedule
JOIN Reference ON
     Schedule.MaybeReferenceId1 = Reference.ReferenceId
     OR Schedule.MaybeReferenceId2 = Reference.ReferenceId
     OR Schedule.MaybeReferenceId3 = Reference.ReferenceId
     OR Schedule.MaybeReferenceId4 = Reference.ReferenceId
     ...

would work. You could simplify it by using IN if your RDBMS supports it:

SELECT *
FROM Schedule
JOIN Reference ON
     Reference.ReferenceId IN (
        Schedule.MaybeReferenceId1,
        Schedule.MaybeReferenceId2,
        Schedule.MaybeReferenceId3,
        Schedule.MaybeReferenceId4,
        ...
     )

Mark Brackett 2009-04-20 20:46:04

Answer 4

A:

Agree with TheSoftwareJedi, but can I just suggest using LEFT JOINs so that failures-to-match don't cause your Schedule row to disappear?

Of course, doing 28 JOINs is going to be a bit cumbersome whatever the details.

I'm not sure I'd call this "denormalized", more "abnormalized" ... :-)

Sharkey 2009-04-20 22:23:57

I suppose it depends: if your db realises that you're joining against the same table, it might then realise that slurping that table into an in-memory hash is a reasonable plan. The schema's not pretty, but I have seen worse.

araqnid 2009-04-20 22:39:05

Answer 5

A:

Try a query like this:

select s.*, r.schareacode from schedule s, 
where 
s.text1_1 = s.schareacode
or s.text2_1 = s.schareacode
or s.textx_x = s.schareacode
..

You should be able to get the same results with traditional joins so I recommend you experiment with that as well.

Helgi 2009-04-21 08:46:17

Answer 6

A:

Hi,

Everyone considered "Select * form" but what if we want only a selected set of columns from both the tables?

I use SQL Server 2005

Amit 2009-07-30 06:04:43

I got the solution.We can use alias names for selecting specified columns eg:SELECT S.col1, R1.Col1, R2.Col2 FROM Schedule AS S INNER JOIN Reference AS R1 ON R1.ID = S.FirstID INNER JOIN Reference AS R2 ON R2.ID = S.SecondID INNER JOIN Reference AS R3 ON R3.ID = S.ThirdID

Amit 2009-07-30 06:40:18

ansaurus

tags:

views:

answers:

Joining multiple columns in one table to a single column in another table

related questions