Following is my table structure.

1. AttributeMaster - This table is a master attribute table that will be available for each and every request.

2. AttributeValue - When the user fills the data from grid, if a column is empty we don't store its value in the database.

For each request, there are multiple line items (TaskId). Every task must have attributes from attribute master. Now, if the user doesn't an attribute, then we don't store it in the database.

In the above scenario, for request 200, for task Id - 1, I only have value for one attribute. For task Id - 2, I have both attributes filled.

The query result should give me 4 rows, 2 for each task ID, with null placeholders in AttrValue column.

select * from AttributeMaster cam 
left join AttributeValue cav on cam.AttrMasterId = cav.AttrMasterId 
and cav.requestId = 36498 and cav.recordStatus = 'A'
right outer join (select distinct AttrMasterId from attrValue cav1 where cav1.requestId = 36498  ) ctI on cti.AttrMasterId = cav.AttrMasterId;

So far, I've tried different joins, tried to self join attribute value table as above, still no results to fill the empty rows.

Any help or pointers would be appreciated. Thanks.

Edit 1: Expected Output is as follows:

CodePudding user response：

Working Fiddle for SQL Server

Since there really should be a Task table, I added that as a CTE term in the first solution. The second form just uses your existing tables directly, with the same result.

WITH Task (TaskId) AS (
        SELECT DISTINCT TaskId FROM AttributeValue
     )
   , pairs (TaskId, AttrMasterId) AS (
        SELECT Task.TaskId, AttributeMaster.AttrMasterId
          FROM AttributeMaster CROSS JOIN Task
     )
SELECT pairs.*
     , AttributeMaster.*
     , cav.*
  FROM pairs
  JOIN AttributeMaster
    ON pairs.AttrMasterId = AttributeMaster.AttrMasterId
  LEFT JOIN AttributeValue AS cav
    ON pairs.AttrMasterId = cav.AttrMasterId AND pairs.TaskId = cav.TaskId
   AND cav.requestId = 200 AND cav.recordStatus = 'A'
 ORDER BY pairs.TaskId, pairs.AttrMasterId
;

 -------- -------------- -------------- ----------------- ----------- ----------- -------- -------------- ------------ -------------- 
| TaskId | AttrMasterId | AttrMasterId | AttrName        | AttrValId | RequestId | TaskId | AttrMasterId | AttrValue  | RecordStatus |
 -------- -------------- -------------- ----------------- ----------- ----------- -------- -------------- ------------ -------------- 
|      1 |            1 |            1 | Expense Items   |         1 |       200 |      1 |            1 | Furniture  | A            |
|      1 |            2 |            2 | Business Reason |      NULL |      NULL |   NULL |         NULL | NULL       | NULL         |
|      2 |            1 |            1 | Expense Items   |         2 |       200 |      2 |            1 | Infra      | A            |
|      2 |            2 |            2 | Business Reason |         3 |       200 |      2 |            2 | Relocation | A            |
 -------- -------------- -------------- ----------------- ----------- ----------- -------- -------------- ------------ -------------- 

The second form is without the added Task CTE term...

WITH pairs AS (
        SELECT DISTINCT AttributeValue.TaskId, AttributeMaster.AttrMasterId
          FROM AttributeMaster CROSS JOIN AttributeValue
     )
SELECT pairs.*
     , AttributeMaster.*
     , cav.*
  FROM pairs
  JOIN AttributeMaster
    ON pairs.AttrMasterId = AttributeMaster.AttrMasterId
  LEFT JOIN AttributeValue AS cav
    ON pairs.AttrMasterId = cav.AttrMasterId AND pairs.TaskId = cav.TaskId
   AND cav.requestId = 200 AND cav.recordStatus = 'A'
 ORDER BY pairs.TaskId, pairs.AttrMasterId
;

CodePudding user response：

Here is another solution that does not require a CTE.
This also uses the TaskID like @jon-armstrong 's answer

declare @AttributeMaster table (MasterID int, Name varchar(50))
declare @AttributeValues table (ValueID int, RequestID int, TaskID int, MasterID int, Value varchar(50), Status varchar(1))

insert into @AttributeMaster (MasterID, Name)
values (1, 'Expense'), (2, 'Business')

insert into @AttributeValues (ValueID, RequestID, TaskID, MasterID, Value, Status)
values (1, 200, 1, 1, 'Furniture', 'A'),
       (2, 200, 2, 1, 'Infra', 'A'),
       (3, 200, 2, 2, 'Relocation', 'A')


select t.RequestID, t.TaskID, t.MasterID, v.Value, v.Status, t.Name
from   ( select distinct m.MasterID, v.TaskID, v.RequestID, m.Name 
         from   @AttributeMaster m cross join @AttributeValues v
       ) t
  left join @AttributeValues v on v.MasterID = t.MasterID and v.TaskID = t.TaskID 
                              and v.RequestID = 200 and v.Status = 'A'

order by t.TaskID, t.MasterID

the result is

RequestID   TaskID  MasterID    Value       Status  Name
200         1       1           Furniture   A       Expense
200         1       2           NULL        NULL    Business
200         2       1           Infra       A       Expense
200         2       2           Relocation  A       Business