I need some logic help with a complex join situation. Here is an overall idea of the 3 tables I am working with

Table 1:

ID Number Type 1

Table 2:

ID Number Type 1 ID Number Type 2
A1 B1
A1 B2
A2 B3
A2 B4

Table 3:

ID Number Type 2 Designation
B1 D 1
B2 D 2
B3 D 1
B4 D 2

I am trying to get this result:

ID Number Type 1 ID Number Type 2 for D 1 ID Number Type 2 for D 2
A1 B1 B3
A2 B2 B4

Basically table 2 results in multiple rows, and I want these multiple resulting ID Type 2’s to show in two separate columns in the resulting table, based on the designation that comes from a third table.

Currently, I have table 1 and table 2, as well as table 2 and table 3, joined with left joins. I am using Case When logic, but it is only resulting in one of the two designation columns getting filled for obvious reasons. Here is my current code:

Case when (Designation = D1) THEN (ID Type 2) END AS [D1]

Case when (Designation = D2) THEN (ID Type 2) END AS [D2]

Can anyone help improve my logic? thank you in advance!

  • 2
    For future reference, please take a look at how to create a minimal reproducible example.
    – Hannah Vernon
    Oct 16, 2022 at 22:22
  • It's not at all clear why A1 ends with B1 and B3 in the result (B1 ok it's from table2 but B3? Same for A2 with B2, B4 (B4 ok, it's form table2 but B2?) Please clarify your rules for what you want in the results. Oct 17, 2022 at 10:20

2 Answers 2


Your "Table 2" mapping won't help you for this task, because it holds non-useful association between A values and B values (A1 is matched with B1 and B2. A2 is matched with B3 and B4).

What you can do here is generate an ad-hoc remapping with ranking values using three window functions:

  • ROW_NUMBER on table 1, to identify the order between A1 and A2
  • DENSE_RANK on table 3, ordering by Designation, to identify the order between designation values

These two will help you generate the association you need: they'll be useful for your join condition betweeen table 1 and 3.

  • ROW_NUMBER on table 3, partitioning by Designation and ordering by ID2, to identify the ordering of rows for each D1 and D2 designations

This one will make you pivot work correctly, selecting the right values you need for each column.

WITH t1 AS (
    SELECT *, 
           ROW_NUMBER() OVER(ORDER BY [ID Number Type 1]) AS rn1
    FROM tab1
), t3 AS (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY Designation 
                             ORDER     BY [ID Number Type 2]) AS rn,
           DENSE_RANK() OVER(ORDER BY Designation) AS rn3
    FROM tab3
SELECT [ID Number Type 1],
       MAX(CASE WHEN rn = 1 THEN [ID Number Type 2] END) AS [ID Number Type 2 for D 1], 
       MAX(CASE WHEN rn = 2 THEN [ID Number Type 2] END) AS [ID Number Type 2 for D 2]
FROM       t1
        ON t1.rn1 = t3.rn3
GROUP BY [ID Number Type 1]

Check the demo here.

  • 1
    I didn't downvote, but I will provide this feedback - windowing functions are a crutch and this is a really awkward way to do things, and additionally I'm not seeing exactly why they'd be necessary.
    – user212533
    Oct 18, 2022 at 13:18
  • I see your point, thanks for sharing! I guess the underlying problem stands with the design itself and attempts like this one are somewhat forced.
    – lemon
    Oct 18, 2022 at 13:36

Your logic is correct, you just need to aggregate in order to return the data at the level you want:

  A.[ID Number Type 1]
        WHEN D.Designation = 'D 1' THEN D.[ID Number Type 2]
        ELSE NULL
    ) AS [ID Number Type 2 for D 1]
        WHEN D.Designation = 'D 2' THEN D.[ID Number Type 2]
        ELSE NULL
    ) AS [ID Number Type 2 for D 2]
  Table1 A
  Table2 B
    ON B.[ID Number Type 1] = A.[ID Number Type 1]
  Table3 D
    ON D.[ID Number Type 2] = B.[ID Number Type 2]
  A.[ID Number Type 1]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.