CODEWITHSUNDEEP
sqlsql-serversimplify
Shaun.S
Shaun.S

Reputation: 81

Is there a simpler way to write this query? [MS SQL Server]

I'm wondering if there is a simpler way to accomplish my goal than what I've come up with.

I am returning a specific attribute that applies to an object. The objects go through multiple iterations and the attributes might change slightly from iteration to iteration. The iteration will only be added to the table if the attribute changes. So the most recent iteration might not be in the table.

Each attribute is uniquely identified by a combination of the Attribute ID (AttribId) and Generation ID (GenId). 

Object_Table
ObjectId  |  AttribId  | GenId
  32      |     2      |   3
  33      |     3      |   1

Attribute_Table
AttribId | GenId | AttribDesc
  1      |   1   |   Text
  2      |   1   |   Some Text
  2      |   2   |   Some Different Text
  3      |   1   |   Other Text

When I query on a specific object I would like it to return an exact match if possible. For example, Object ID 33 would return "Other Text".

But if there is no exact match, I would like for the most recent generation (largest Gen ID) to be returned. For example, Object ID 32 would return "Some Different Text". Since there is no Attribute ID 2 from Gen 3, it uses the description from the most recent iteration of the Attribute which is Gen ID 2.

This is what I've come up with to accomplish that goal:

SELECT attr.AttribDesc
FROM Attribute_Table AS attr
JOIN Object_Table AS obj
  ON obj.AttribId = obj.AttribId
WHERE attr.GenId = (SELECT MIN(GenId)
                        FROM(SELECT CASE obj2.GenId
                             WHEN attr2.GenId THEN attr2.GenId
                             ELSE(SELECT MAX(attr3.GenId)
                                   FROM Attribute_Table AS attr3
                                   JOIN Object_Table AS obj3
                                      ON obj3.AttribId = attr3.AttribId
                                   WHERE obj3.AttribId = 2
                                  )
                             END AS GenId
                             FROM Attribute_Table AS attr2
                             JOIN Object_Table AS obj2
                               ON attr2.AttribId = obj2.AttribId
                             WHERE obj2.AttribId = 2
                             ) AS ListOfGens
)

Is there a simpler way to accomplish this? I feel that there should be, but I'm relatively new to SQL and can't think of anything else.

Thanks!

Upvotes: 2

Views: 79

Answers (3)

Ed B
Ed B

Reputation: 796

The following query will return the matching value, if found, otherwise use a correlated subquery to return the value with the highest GenId and matching AttribId:

SELECT obj.Object_Id,
       CASE WHEN attr1.AttribDesc IS NOT NULL THEN attr1.AttribDesc ELSE attr2.AttribDesc END AS AttribDesc
FROM Object_Table AS obj
LEFT JOIN Attribute_Table AS attr1
  ON attr1.AttribId = obj.AttribId AND attr1.GenId = obj.GenId
LEFT JOIN Attribute_Table AS attr2
  ON attr2.AttribId = obj.AttribId AND attr2.GenId = (
        SELECT max(GenId)
        FROM Attribute_Table AS attr3
        WHERE attr3.AttribId = obj.AttribId)

In the case where there is no matching record at all with the given AttribId, it will return NULL. If you want to get no record at all in this case, make the second JOIN an INNER JOIN rather than a LEFT JOIN.

Upvotes: 2

dean
dean

Reputation: 10098

This should work:

with x as (
    select *, row_number() over (partition by AttribId order by GenId desc) as rn
    from Attribute_Table
)
select isnull(a.attribdesc, x.attribdesc)
from Object_Table o
left join Attribute_Table a
on o.AttribId = a.AttribId and o.GenId = a.GenId
left join x on o.AttribId = x.AttribId and rn = 1

Upvotes: 0

SoulTrain
SoulTrain

Reputation: 1904

Try this...

Incase the logic doesn't find a match for the Object_table GENID it maps it to the next highest GENID in the ON clause of the JOIN.

 SELECT AttribDesc
    FROM object_TABLE A
    INNER JOIN Attribute_Table B
        ON A.AttrbId = B.AttrbId
            AND (
                CASE 
                    WHEN A.Genid <> B.Genid
                        THEN (
                                SELECT MAX(C.Genid)
                                FROM Attribute_Table C
                                WHERE A.AttrbId = C.AttrbId
                                )
                    ELSE A.Genid
                    END
                ) -- Selecting the right GENID in the join clause should do the job
                = B.Genid

Upvotes: 0

Related Questions