0 votes
1 view
in BI by (43.3k points)

I have two tables one is the lookup table and the other is the data table. The lookup table has columns named cycleid, cycle. The data table has SID, cycleid, cycle. Below is the structure of the tables.

Data Table

Lookup Table

If you check the data table, the SID may have all the cycles and may not have all the cycles. I want to output the SID completed as well as missed cycles.

I right joined the lookup table and retrieved the missing as well as completed cycles. Below is the query I used. 

 SELECT TOP 1000 [SID]

          ,s4.[CYCLE]

          ,s4.[CYCLEID]

      FROM [dbo].[data] s3 RIGHT JOIN

[dbo].[lookup_data] s4 ON s3.CYCLEID = s4.CYCLEID 

The query is not displaying me the missed values when I query for all the SID's. When I specifically query for a SID with the below query I am getting the correct result including the missed ones.

SELECT TOP 1000 [SID]

      ,s4.[CYCLE]

      ,s4.[CYCLEID]

  FROM [dbo].[data] s3 RIGHT JOIN [dbo].[lookup_data] s4 

  ON s3.CYCLEID = s4.CYCLEID 

  AND s3.SID = 101002

  ORDER BY [SID], s4.[CYCLEID]

As I am supplying this query into tableau I cannot provide the sid value in the query. I want to return all the sid's and from tableau, I will be doing the rest of the things.

The expected output that I need is as shown below. 

Expected Output

I wrote a cross join query like below to achieve my expected output

SELECT DISTINCT

            tab.CYCLEID

           ,tab.SID

           ,d.CYCLE

  FROM      ( SELECT    d.SID

                       ,d.[CYCLE]

                       ,e.CYCLEID

              FROM      ( SELECT    e.sid

                                   ,e.CYCLE

                          FROM      [db_temp].[dbo].[Sheet3$] e

                        ) d

              CROSS JOIN [db_temp].[dbo].[Sheet4$] e

            ) tab

  LEFT OUTER JOIN [db_temp].[dbo].[Sheet3$] d

  ON        d.CYCLEID = tab.CYCLEID

            AND d.SID = tab.SID

  ORDER BY  tab.SID

           ,tab.CYCLEID;

However, I am not able to use this query for more scenarios as my data set to have nearly 20 to 40 columns and I am having issues when I use the above one.

Is there any way to do this in a simpler manner with only left or right join itself? I want the query to return all the missing values and the completed values for all the SID's instead of supplying a single sid in the query. 

1 Answer

0 votes
by (16.3k points)
  • You can create a master table first (combine all SID and CYCLE ID), then right join with the data table

;with ctxMaster as (

    select distinct d.SID, l.CYCLE, l.CYCLEID

    from lookup_data l

    cross join data d

)

select d.SID, m.CYCLE, m.CYCLEID

from ctxMaster m

left join data d on m.SID = d.SID and m.CYCLEID = d.CYCLEID

order by m.SID, m.CYCLEID

  • Click on this link to see the execution: Fiddle

  • In case, if you don't want to use common table expression, subquery version:

select d.SID, m.CYCLE, m.CYCLEID

from (select distinct d.SID, l.CYCLE, l.CYCLEID

      from lookup_data l

      cross join data d) m

left join data d on m.SID = d.SID and m.CYCLEID = d.CYCLEID

order by m.SID, m.CYCLEID

Related questions

0 votes
1 answer
asked Dec 1, 2019 in BI by Vaibhav Ameta (16.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...