CODEWITHSUNDEEP
sqlsql-serverstored-proceduresjoin
Shukhrat Raimov
Shukhrat Raimov

Reputation: 2247

How to correctly join table which may return more than one row

I have a table Items which have the following columns:

ItemId || AdminUserID || ItemName

And I try to join it with ItemAttributes:

ItemAttributeId || ItemId || AttributeValue

where one ItemId can have several ItemAttributeId values assigned to it.

I created procedure to retrieve item values from two tables:

 SELECT 
    i.ItemID,
    i.AdminUserID,
    i.ItemName,
    attr1.AttributeValue as IsForEmailRobotProcessingValue,
    attr2.AttributeValue as RobotScheduledHoursValue -- HERE IS THE PROBLEM
FROM [dbo].[Items] i WITH (NOLOCK)
    LEFT JOIN [dbo].ItemAttributes attr1 WITH(NOLOCK)
        ON attr1.ItemID = i.ItemID and attr1.ItemAttributeID = 1
    LEFT JOIN [dbo].ItemAttributes attr2 WITH(NOLOCK)
    ON attr2.ItemID = i.ItemID and attr2.ItemAttributeID = 2
WHERE ((attr1.AttributeValue IS NOT NULL AND attr1.AttributeValue = 'true') OR 
    @OnlyForEmailRobotProcessing = 0)
ORDER BY ItemName

What I don't like here is that I LEFT JOIN two times the same table and created different aliases for each join according to the same ItemId. Is there any better way to LEFT JOIN only one time and created two different aliases for each ItemAttributeId? Thanks!

Upvotes: 1

Views: 42

Answers (1)

Milen
Milen

Reputation: 8867

Try this:

SELECT 
    i.ItemID,
    i.AdminUserID,
    i.ItemName,
    attr1.AttributeValue1 as IsForEmailRobotProcessingValue,
    attr1.AttributeValue2 as RobotScheduledHoursValue 
FROM [dbo].[Items] i WITH (NOLOCK)
    LEFT JOIN
            (select ItemId,  (select AttributeValue 
                              from  [dbo].ItemAttributes a
                              where a.ItemId = b.ItemId and a.ItemAttributeID = 1) as AttributeValue1,
                              (select AttributeValue 
                              from  [dbo].ItemAttributes c
                              where c.ItemId = b.ItemId and c.ItemAttributeID = 2) as AttributeValue2
             from    [dbo].ItemAttributes b  WITH(NOLOCK)
             where b.ItemId = i.ItemID  and b.ItemAttributeID in (1,2)

                ) attr1 on i.ItemId  = attr1.ItemId

WHERE ((attr1.AttributeValue IS NOT NULL AND attr1.AttributeValue = 'true') OR 
    @OnlyForEmailRobotProcessing = 0)
ORDER BY ItemName

Upvotes: 1

Related Questions