Simplify 30 columns left joined to same table

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Simplify 30 columns left joined to same table

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2014-11-27 : 06:10:39

I have a scenario where I want to simplify my SQL.

I have a lookup table Called Accounts (AccNo, AccName)

Then I have a table called Items that has 200 columns with varying column names. ItemNo, ItemName, NA1,NA2, StartAnnNo, LostAccNo, PriceDiffAccNo etc

Instead of LEFT joining each Foreign Key column in Item to Accounts

e.g. Select ItemNo,ItemName
,a1.AccName as StartAccName
,a2.AccName as LostAccName
,a3.AccName as PriceDiffAccName
From Items I
LEFT JOIN Accounts A1 on A1.AccNo=I.StartAccNo
LEFT JOIN Accounts A2 on A2.AccNo=I.LostAccNo
LEFT JOIN Accounts A3 on A3AccNo=I.PriceDiffAccNo
etc

I want to automate this without having to left join alias each time and just by passing the column names or just the wildcard %Acc because there is about 30 account column name.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 10:26:53

Why are you not using AND in the JOINs? e.g.


LEFT JOIN Accounts A1 on A1.AccNo=I.StartAccNo
              AND  A1.AccNo=I.LostAccNo
              AND  A1 on A3AccNo=I.PriceDiffAccNo
etc

Also, you can use dynamic SQL to do what you want.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-27 : 10:52:32

The only alternative to lots of JOINs I can thing of is to
1. UNPIVOT the AccNo columns in Items
2. Join the UNPIVOTed columns to Accounts
3. PIVOT the AccNo columns again
4. JOIN the result to Items

I am not sure this will be any better than lots of JOINS.

If you take the time to post some consumable test data, someone might provide you with outline code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources