alternative solution to too many JOINs

There is a table containing all names:

CREATE TABLE Names(
  Name VARCHAR(20)
)

And there are multiple tables with similar schema. Let's say:

CREATE TABLE T1
(
  Name VARCHAR(20),
  Description VARCHAR(30),
  Version INT
)

CREATE TABLE T2
(
  Name VARCHAR(20),
  Description VARCHAR(30),
  Version INT
)

I need to query description for each name, by following priority:

  1. any records in T1 with matching name and version = 1
  2. any records in T1 with matching name and version = 2
  3. any records in T2 with matching name and version = 1
  4. any records in T2 with matching name and version = 2

I want result from lower priority source only if there are no result from higher priority source.

So far that's I've got:

SELECT 
N.Name AS Name, Description = 
CASE
WHEN (T11.Description IS NOT NULL) THEN T11.Description
WHEN (T12.Description IS NOT NULL) THEN T12.Description
WHEN (T21.Description IS NOT NULL) THEN T21.Description
WHEN (T22.Description IS NOT NULL) THEN T22.Description
ELSE NULL
END
FROM Names AS N
LEFT JOIN T1 AS T11 ON T11.Name = N.Name AND T11.Version = 1 
LEFT JOIN T1 AS T12 ON T12.Name = N.Name AND T12.Version = 2
LEFT JOIN T2 AS T21 ON T21.Name = N.Name AND T21.Version = 1 
LEFT JOIN T2 AS T22 ON T22.Name = N.Name AND T22.Version = 2

It's working, but are there too much JOIN here? Is there any better approach?

sqlfiddle

Sample Input:

INSERT INTO Names VALUES('name1')
INSERT INTO Names VALUES('name2')
INSERT INTO Names VALUES('name3')
INSERT INTO Names VALUES('name4')
INSERT INTO Names VALUES('name5')
INSERT INTO Names VALUES('name6')

INSERT INTO T1 VALUES ('name1','name1_T1_1', 1)
INSERT INTO T1 VALUES ('name2','name2_T1_1', 1)
INSERT INTO T1 VALUES ('name3','name3_T1_1', 1)
INSERT INTO T1 VALUES ('name3','name3_T1_2', 2)
INSERT INTO T1 VALUES ('name5','name5_T1_2', 2)

INSERT INTO T2 VALUES ('name1','name1_T2_1', 1)
INSERT INTO T2 VALUES ('name4','name4_T2_1', 1)

Excepted result:

--
--  Excepted result:
--  Name      Description
--  name1      name1_T1_1
--  name2      name2_T1_1
--  name3      name3_T1_1
--  name4      name4_T2_1
--  name5      name5_T1_2
--  name6      NULL

Answers


Well, this is a solution to eliminate the case statement and minimize the repetitive part of the query, it requires some joins of it's own of course, so you'd need quite some tables and/or versions to get any real benefit out of it:

;WITH
AllDescriptions AS
(
    SELECT 1 AS Rank, * FROM T1
    UNION ALL SELECT 2 AS Rank, * FROM T2
    -- UNION ALL SELECT 3 AS Rank, * FROM T3
    -- UNION ALL SELECT 4 AS Rank, * FROM T4
    -- etc
),
Ranks AS
(
    SELECT
        AllDescriptions.Name,
        MIN(AllDescriptions.Rank) AS Rank
    FROM
        AllDescriptions
    GROUP BY
        Name
),
Versions AS
(
    SELECT
        AllDescriptions.Name,
        AllDescriptions.Rank,
        MIN(AllDescriptions.Version) AS Version
    FROM
        AllDescriptions
        INNER JOIN Ranks
            ON Ranks.Name = AllDescriptions.Name
            AND Ranks.Rank = AllDescriptions.Rank
        GROUP BY
            AllDescriptions.Name,
            AllDescriptions.Rank
),
Descriptions AS
(
    SELECT
        AllDescriptions.Name,
        AllDescriptions.Description
    FROM
        AllDescriptions
        INNER JOIN Versions
            ON Versions.Name = AllDescriptions.Name
            AND Versions.Rank = AllDescriptions.Rank
            AND Versions.Version = AllDescriptions.Version
)
SELECT
    Names.*,
    Descriptions.Description
FROM
    Names
    LEFT OUTER JOIN Descriptions
        ON Descriptions.Name = Names.Name

select n.name, isnull(d.description,d1.Description) description
from Names n
outer apply (select top 1 t1.Name, t1.Description
             from T1
             WHERE t1.Name = n.name
             order by Version asc   
            ) d
outer apply (select top 1 t2.Name, t2.Description
            from T2
             WHERE t2.Name = n.name
             order by Version asc   
            ) d1

Need Your Help

How to render Html page in jsp

jsp web.xml

Greeting,I want to render the whole html page in jsp and do some operation like replace the text according to code before run on server.Is it possible.Actually I am new in java Programming.In .net ,I

Egit understanding FETCH_HEAD behavior

eclipse egit

Created a repo on Github (initialized with a readme). Cloned it with Egit. Saw that FETCH_HEAD and HEAD were checked out. Went to Github and made a change to the readme. Fetched the change. Saw that

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.