INNER JOIN and bad table design

[Michael B Mueller]

Hi.

 

I stumpled into an old project of a long gone employee.

The tables he used are realy bad, seems he used to work with Excel and had not realy a clue on how databases work

 

Columns Table #1:

S, LastName, FirstName

 

Columns Table #2:

ID, S1, S2, S3, S4, S5, S6 ,S7, S8, S10, S11, S12, S13, S14, S15, S16

 

The problem is to do a query retrieving all LastName and ForeName of #2 while all S* in #1 refer to S in #2.

 

Right now I'm doing the following query:

 

SELECT A.ID, B1.LastName, B1.ForeName, B2.LastName, B2.ForeName, B3.LastName, B3.ForeName, B4.LastName, B4.ForeName, B5.LastName, B5.ForeName, B6.LastName, B6.ForeName, B7.LastName, B7.ForeName, B8.LastName, B8.ForeName, B9.LastName, B9.ForeName, B10.LastName, B10.ForeName, B11.LastName, B11.ForeName, B12.LastName, B12.ForeName, B13.LastName, B13.ForeName, B14.LastName, B14.ForeName, B15.LastName, B15.ForeName, B16.LastName, B16.ForeNameFROM ((((((((((((((( Table2 AS A INNER JOIN Table1 AS B1 ON A.S1=B1.ID) INNER JOIN Table1 AS B2 ON A.S2=B2.ID) INNER JOIN Table1 AS B3 ON A.S3=B3.ID) INNER JOIN Table1 AS B4 ON A.S4=B4.ID) INNER JOIN Table1 AS B5 ON A.S5=B5.ID) INNER JOIN Table1 AS B6 ON A.S6=B6.ID) INNER JOIN Table1 AS B7 ON A.S7=B7.ID) INNER JOIN Table1 AS B8 ON A.S8=B8.ID) INNER JOIN Table1 AS B9 ON A.S9=B9.ID) INNER JOIN Table1 AS B10 ON A.S10=B10.ID) INNER JOIN Table1 AS B11 ON A.S11=B11.ID) INNER JOIN Table1 AS B12 ON A.S12=B12.ID) INNER JOIN Table1 AS B13 ON A.S13=B13.ID) INNER JOIN Table1 AS B14 ON A.S14=B14.ID) INNER JOIN Table1 AS B15 ON A.S15=B15.ID) INNER JOIN Table1 AS B16 ON A.S16=B16.ID;

Lot's of "INNER JOIN" - working, but can it be solved more elegant, without to change the tables, like a reduced SQL-String?

 

I'm aware that changing the tables certainly would be the best solution.

But due to some other queries, which I can not change, the tables must be kept like they are.

Otherwise the whole project would probably be f****d up.

 

Thanks for any hint.

 

Michi

Edited October 14, 2013 by Michael B Mueller

[davej]

So S is the primary key in table #1. Can you explain what a typical row in table #2 describes?

[Michael B Mueller]

Hi davej,

 

yes, you are right, S is the primary key in table #1 wich is kind of 10-digit user identification.

The table #2 lists the S*, whcih are the 10-digit user identification of ... let's say the participants of a recurrent meeting.

 

It's a bit more complex, and the table #2 is way bigger, containing numerous other columns, but to keep it simple I stripped down the problem a bit

 

Michi

Edited October 16, 2013 by Michael B Mueller

[davej]

Columns Table #1:

S, LastName, FirstName

 

Columns Table #2:

ID, S1, S2, S3, S4, S5, S6 ,S7, S8, S10, S11, S12, S13, S14, S15, S16

 

So ID is the identifier for a particular meeting?

S1 is a person who attended that meeting?

S2 is another person who attended that meeting?

 

That CAN'T be the answer because that would NOT be a legitimate database normalization.

 

http://en.wikipedia.org/wiki/First_normal_form

 

I would think the best idea would be to either generate temporary tables to use during processing or to replace these tables.

Edited October 19, 2013 by davej