SQL Server Join Left on a Subset [closed]

Closed. This question needs to be more focused . It is not currently accepting answers.

Want to improve this question? Update the question so it focuses on one problem only by editing this post .

Closed 4 days ago.
Improve this question

I have two different table obtained by two different queries as shown below:

FIRST TABLE:

SELECT machine, item, kit FROM table1
| MACHINE | ITEM | KIT |
| ------------ | ------------ | ------------ |
| 100 | FV123 | Z_00001_01 |
| 101 | KN456 | Z_00008_10 |
| ... | ... | ... |

SECOND TABLE:

SELECT req, kit, comp FROM table2
| REQ | KIT | COMP |
| ------------ | ------------ | ------------ |
| 1 | Z_00001_01 | N-11111_01 |
| 1 | Z_00001_01 | N-22222_01 |
| 1 | Z_00001_01 | N-33333_01 |
| 5 | Z_00001_01 | N-11111_01 |
| 5 | Z_00001_01 | N-22222_01 |
| 2 | Z_00008_10 | P-11111_01 |
| 2 | Z_00008_10 | P-22222_01 |
| 2 | Z_00008_10 | P-33333_01 |
| 4 | Z_00008_10 | P-11111_01 |
| 4 | Z_00008_10 | P-22222_01 |
| ... | ... | ... |

I am trying, unfortunately not succeeding so far, to reach the following goal:

Order the second table by REQ column in DESC order;
| REQ | KIT | COMP |
| ------------ | ------------ | ------------ |
| 5 | Z_00001_01 | N-11111_01 |
| 5 | Z_00001_01 | N-22222_01 |
| 1 | Z_00001_01 | N-11111_01 |
| 1 | Z_00001_01 | N-22222_01 |
| 1 | Z_00001_01 | N-33333_01 |
| 4 | Z_00008_10 | P-11111_01 |
| 4 | Z_00008_10 | P-22222_01 |
| 2 | Z_00008_10 | P-11111_01 |
| 2 | Z_00008_10 | P-22222_01 |
| 2 | Z_00008_10 | P-33333_01 |
| ... | ... | ... |
Create subset by the max of the REQ column;
| REQ | KIT | COMP |
| ------------ | ------------ | ------------ |
| 5 | Z_00001_01 | N-11111_01 |
| 5 | Z_00001_01 | N-22222_01 |
| 4 | Z_00008_10 | P-11111_01 |
| 4 | Z_00008_10 | P-22222_01 |
| ... | ... | ... |
Using the common KIT column as link perform a LEFT JOIN in the first query to bring the values of the COMP column in the first output table.
| MACHINE | ITEM | KIT | COMP |
| ------------ | ------------ | ------------ | ------------ |
| 100 | FV123 | Z_00001_01 | N-11111_01 |
| 100 | FV123 | Z_00001_01 | N-22222_01 |
| 101 | KN456 | Z_00008_10 | P-11111_01 |
| 101 | KN456 | Z_00008_10 | P-22222_01 |
| ... | ... | ... | ... |

Any ideas how can I achieve the result I want?

This is one way to do it:

WITH comps AS (
    SELECT  *
    FROM    (
    VALUES  (100, N'FV123', N'Z_00001_01')
    ,   (101, N'KN456', N'Z_00008_10')
) t (MACHINE,ITEM,KIT)
)
, kits AS (
    SELECT  *
    FROM    (
    VALUES  (1, N'Z_00001_01', N'N-11111_01')
    ,   (1, N'Z_00001_01', N'N-22222_01')
    ,   (1, N'Z_00001_01', N'N-33333_01')
    ,   (5, N'Z_00001_01', N'N-11111_01')
    ,   (5, N'Z_00001_01', N'N-22222_01')
    ,   (2, N'Z_00008_10', N'P-11111_01')
    ,   (2, N'Z_00008_10', N'P-22222_01')
    ,   (2, N'Z_00008_10', N'P-33333_01')
    ,   (4, N'Z_00008_10', N'P-11111_01')
    ,   (4, N'Z_00008_10', N'P-22222_01')
) t (REQ,KIT,COMP)
)
SELECT  c.MACHINE, c.ITEM, x.KIT, x.COMP
FROM    (
    SELECT  *
    ,   DENSE_RANK() OVER(PARTITION BY KIT ORDER BY Req DESC) AS sort
    FROM    kits
    ) x
LEFT JOIN comps c
    ON  c.KIT = x.KIT
WHERE   sort = 1

DENSE_RANK() OVER(PARTITION BY KIT ORDER BY Req DESC) AS sort creates a sort column grouped by KIT which goes from highest Req to lowest.
WHERE sort = 1 gets the first value in the above sort
Then you can just do the LEFT JOIN to get comp-data.

SQL Server Join Left on a Subset [closed]

1条答案

相关问题

热门标签

最新问答