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

Question

I am looking to create a view that pulls data from two tables "Schedule" and "Reference".

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.

How do I write the SQL statement to correctly join each column in Schedules to the single column in Reference?

The Schedule table is defined as:

    CREATE TABLE [dbo].[Schedule](
    [ID] [int] NOT NULL,
    [SCHEDULEWEEK] [datetime] NOT NULL,
    [EMPNO] [numeric](10, 0) NOT NULL,
    [EMPLNAME] [varchar](32) NULL,
    [EMPFNAME] [varchar](32) NULL,
    [EMPSENDATE] [datetime] NULL,
    [EMPHIREDATE] [datetime] NULL,
    [EMPTYPE] [char](1) NULL,
    [EMPSTATUS] [char](1) NULL,
    [SNREFUSALS] [tinyint] NULL,
    [QUALSTRING] [varchar](128) NULL,
    [JOBOVERSHIFTTYPE] [bit] NULL,
    [SHORTNOTICE] [bit] NULL,
    [SHORTNOTICEWAP] [bit] NULL,
    [SHORTNOTICEPHONE] [varchar](32) NULL,
    [LEADHAND] [bit] NULL,
    [DUALCURRENCY] [bit] NULL,
    [MIN100WINDOW] [bit] NULL,
    [STATHOLIDAY] [bit] NULL,
    [AREAOVERHOURS] [bit] NULL,
    [DOUBLEINTERZONES] [bit] NULL,
    [MAXDAYSPERWEEK] [tinyint] NULL,
    [MAXHOURSPERWEEK] [numeric](10, 2) NULL,
    [MAXHOURSPERSHIFT] [numeric](10, 2) NULL,
    [MAXDOUBLESPERWEEK] [tinyint] NULL,
    [ASSIGNEDDAYS] [tinyint] NULL,
    [ASSIGNEDHOURS] [numeric](10, 2) NULL,
    [ASSIGNEDDOUBLES] [tinyint] NULL,
    [ASSIGNEDLOAHOURS] [numeric](10, 2) NULL,
    [SHIFTNO1] [int] NULL,
    [TEXT1_1] [varchar](64) NULL,
    [TEXT2_1] [varchar](64) NULL,
    [DAYFLAG1] [bit] NULL,
    [COMMENT1] [text] NULL,
    [SHIFTNO2] [int] NULL,
    [TEXT1_2] [varchar](64) NULL,
    [TEXT2_2] [varchar](64) NULL,
    [DAYFLAG2] [bit] NULL,
    [COMMENT2] [text] NULL,
    [SHIFTNO3] [int] NULL,
    [TEXT1_3] [varchar](64) NULL,
    [TEXT2_3] [varchar](64) NULL,
    [DAYFLAG3] [bit] NULL,
    [COMMENT3] [text] NULL,
    [SHIFTNO4] [int] NULL,
    [TEXT1_4] [varchar](64) NULL,
    [TEXT2_4] [varchar](64) NULL,
    [DAYFLAG4] [bit] NULL,
    [COMMENT4] [text] NULL,
    [SHIFTNO5] [int] NULL,
    [TEXT1_5] [varchar](64) NULL,
    [TEXT2_5] [varchar](64) NULL,
    [DAYFLAG5] [bit] NULL,
    [COMMENT5] [text] NULL,
    [SHIFTNO6] [int] NULL,
    [TEXT1_6] [varchar](64) NULL,
    [TEXT2_6] [varchar](64) NULL,
    [DAYFLAG6] [bit] NULL,
    [COMMENT6] [text] NULL
-- Snip
) ON [PRIMARY]

And the Reference table is defined as:

CREATE TABLE [dbo].[Reference](
    [ID] [int] NOT NULL,
    [CODE] [varchar](21) NOT NULL,
    [LOCATIONCODE] [varchar](4) NOT NULL,
    [SCHAREACODE] [varchar](16) NOT NULL,
    [LOCATIONNAME] [varchar](32) NOT NULL,
    [FLTAREACODE] [varchar](16) NOT NULL
) ON [PRIMARY]

I am trying to join each [TEXT1_]/[TEXT2_] column in Schedule to the [SCHAREACODE] column in reference. All the reference table contains is a list of areas where the employee could work.

Please update your question with an example of your tables and which RDBMS you're using - e.g. MySQL, SQL Server, etc. — Seb, Apr 20 '09 at 20:33
Does every column in Schedules join to a COLUMN in reference - or do you actually mean to a ROW? Please provide a example (eg. 3 of the 50 columns.) — Stefan Steinegger, Apr 20 '09 at 20:33
This is really funny. You mean, TEXT*_* is a foreign key to SHAREACODE? Is SHAREACODE unique in the Reference table? OR could it point to many references? I ask because it does not use the primary key ... — Stefan Steinegger, Apr 20 '09 at 20:50
Yes, TEXT*_* is a foreign key to SHAREACODE. Essentially, each row in Schedule refers to a schedule for a week for an employee. One row, one week. 28 possible shifts, so 28 columns. Well, 56 columns because there is a note associated with the shift... — Steve Syfuhs, Apr 20 '09 at 21:20

score 7 · Answer 1 · answered Apr 20 '09 at 20:43

7

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

answered Apr 20 '09 at 20:43

TheSoftwareJedi

34,421
21
109
151

1

What if I want to select few columns from Reference Table?Would it be like "Select R1.ID, R2.ID, R3.ID, R4.ID ......." – nakul Mar 09 '16 at 11:33

score 2 · Answer 2 · answered Apr 20 '09 at 20:46

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,
        ...
     )

beach · Answer 3 · 2009-04-20T20:57:24.157

0

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

edited Apr 20 '09 at 20:57

answered Apr 20 '09 at 20:40

beach

8,330
3
29
25

I suspect this might be the only way. – Steve Syfuhs Apr 20 '09 at 21:21

score 0 · Answer 4 · answered Apr 20 '09 at 22:23

0

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" ... :-)

answered Apr 20 '09 at 22:23

NickZoic

7,575
3
25
18

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 Apr 20 '09 at 22:39

score 0 · Answer 5 · answered Apr 21 '09 at 08:46

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.

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

5 Answers5

Linked