CODEWITHSUNDEEP
sql-serversql-server-2008t-sqljoin
bendataclear
bendataclear

Reputation: 3850

Join table to another with multiple foreign keys to the same table

I have a table with category codes which listed in another table, there are 8 category codes and currently I'm doing the below (below is massively simplified):

SELECT HeaderCode
     , C1.CategoryName AS C1
     , C2.CategoryName AS C2
     , C3.CategoryName AS C3
     , C4.CategoryName AS C4
     , C5.CategoryName AS C5
     , C6.CategoryName AS C6
     , C7.CategoryName AS C7
     , C8.CategoryName AS C8


FROM Header H
INNER JOIN Cats C1 ON H.Cat1 = C1.CategoryID
INNER JOIN Cats C2 ON H.Cat2 = C2.CategoryID
INNER JOIN Cats C3 ON H.Cat3 = C3.CategoryID
INNER JOIN Cats C4 ON H.Cat4 = C4.CategoryID
INNER JOIN Cats C5 ON H.Cat5 = C5.CategoryID
INNER JOIN Cats C6 ON H.Cat6 = C6.CategoryID
INNER JOIN Cats C7 ON H.Cat7 = C7.CategoryID
INNER JOIN Cats C8 ON H.Cat8 = C8.CategoryID

SQL Fiddle Example

I did think about a function to get the data but it would be slow as there are 200,000+ records I need to get this information for.

Performance is so far fine but there are times I have to use dynamic SQL and doing this with another 5+ joins is a big mess.

Is there a better/more convenient/easier to maintain way to do this?

Upvotes: 0

Views: 610

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11773

Assuming you can't fix your schema (meaning, normalize your schema), you could create an Inline(!) Table Valued Function like this:

CREATE FUNCTION dbo.EightCats(@C1 INT, @C2 INT, @C3 INT, ...)
RETURNS TABLE
AS
RETURN
 SELECT * 
   FROM (SELECT CategoryName AS C1 FROM dbo.Cats WHERE CategoryId = @C1) T1
   CROSS JOIN (SELECT CategoryName AS C2 FROM dbo.Cats WHERE CategoryId = @C2) T2
   CROSS JOIN (SELECT CategoryName AS C3 FROM dbo.Cats WHERE CategoryId = @C2) T3
   ...

Then you could write your query like this:

SELECT H.HeaderCode, EC.*
  FROM dbo.Header H
 CROSS APPLY dbo.EightCats(H.Cat1,H.Cat2,H.Cat3,H.Cat4,H.Cat5,H.Cat6,H.Cat7,H.Cat8) EC;

This should result in the same execution plan so there should not be a performance penalty for this. Important is that you use this for eight categories only. if you have another query that has only seven categories, you need to create a new function. Otherwise you (or rather SQL Server) will end up doing unnecessary joins.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

I would do this with a bridge table.

CREATE TABLE HeaderCategory (
  HeaderCategoryId int IDENTITY(1,1) NOT NULL
, HeaderId int
, CategoryId int
, SortBy int
)

And make HeaderId and CategoryId foreign keys to their respective tables. The SortBy column is used to indicate whether it's Category1, 2, 3, etc.

This is the more "normalized" architecture.

Then to get the same query output that you are getting in your question, do a simple PIVOT query.

If you don't want to change your tables, two other possibilities: 1) Write a GetCategoryName(CategoryId) function, and use it in your select like this: SELECT Header, GetCategoryName(Category1) AS C1, ...

2) Create a VIEW that contains the CategoryNames.

Upvotes: 0

Related Questions