Left join to only return one row from matching link in the right table11.03.26

```

Select F.*, B.*
from Foo f outer apply
     (select top 1 b.*
      from bar b
      where f.Id = B.Id
     ) b;

```

Okay, focusing solely on the `OUTER APPLY` part:

```sql
OUTER APPLY
    (SELECT TOP 1 l.*
     FROM SL.dbo.hzs2_supplier_order_ln AS l
     WHERE t.order_no = l.hzs2_order_no
       AND t.style = l.hzs2_style_code
     ORDER BY l.hzs2_line DESC
    ) AS l
```

```

outer apply
     (select top 1 l.*
      from SL.dbo.hzs2_supplier_order_ln  AS l
      where t.order_no = l.hzs2_order_no
      AND t.style = l.hzs2_style_code
      ORDER BY l.hzs2_line desc 
     ) l 
```

**Breakdown:**

1. **`OUTER APPLY`**: This is the operator that makes the magic happen. It's like a special type of `JOIN` that allows you to run a subquery for each row of the table on the "outside" of the `APPLY`.

2. **`(...)`**: Inside the parentheses is a subquery. This subquery is designed to find related data in the `SL.dbo.hzs2_supplier_order_ln` table.  Crucially, this subquery can *reference columns from the outer table* (that's `t.order_no` and `t.style`).

3. **`SELECT TOP 1 l.* ...`**:  This subquery selects the *single* row from `SL.dbo.hzs2_supplier_order_ln` that meets certain criteria.  The `TOP 1` is essential because `OUTER APPLY` expects the subquery to return either zero rows or one row.

4. **`FROM SL.dbo.hzs2_supplier_order_ln AS l`**: This specifies the table from which the subquery retrieves data.

5. **`WHERE t.order_no = l.hzs2_order_no AND t.style = l.hzs2_style_code`**: This is the *correlation*.  It's the link between the outer table (`t`) and the subquery.  For each row in `t`, the subquery will only consider rows in `SL.dbo.hzs2_supplier_order_ln` where the `hzs2_order_no` matches the `order_no` from the current row in `t`, *and* where the `hzs2_style_code` matches the `style` from the current row in `t`.  This is how the subquery finds the rows in `SL.dbo.hzs2_supplier_order_ln` that are related to the current row in `t`.

6. **`ORDER BY l.hzs2_line DESC`**: This sorts the matching rows in `SL.dbo.hzs2_supplier_order_ln` by the `hzs2_line` column in descending order.  The row with the *highest* `hzs2_line` value will be at the top.

7. **`AS l`**: This assigns the alias `l` to the *result* of the subquery.  This alias is used to refer to the columns from the subquery in the main `SELECT` statement.

**In simpler terms:**

Imagine you're going through each row in your `your_main_table` one by one. For each row, the `OUTER APPLY` does this:

1. It finds all the rows in `SL.dbo.hzs2_supplier_order_ln` that have the same `order_no` and `style` as the current row in `your_main_table`.
2. It sorts those matching rows by `hzs2_line` in descending order (highest `hzs2_line` first).
3. It picks the *very first* row from that sorted list (the one with the highest `hzs2_line`).
4. It then "joins" (or appends) the data from that single row to the current row from `your_main_table`.

If there are *no* matching rows in `SL.dbo.hzs2_supplier_order_ln` for a particular row in `your_main_table`, the `OUTER APPLY` will still return a row, but all the columns from `SL.dbo.hzs2_supplier_order_ln` will be `NULL`. This is the "outer" part of `OUTER APPLY` – it guarantees that you get a row for every row in the outer table, even if there's no match in the inner table.

The `hzs2_line` is assumed to be some kind of line number or identifier that indicates which row is the "latest" or most relevant.  If a higher `hzs2_line` doesn't actually mean "latest", then this query will not give you the latest record.  You would need to change the `ORDER BY` clause to use a different column that represents the date or time of the record.

tags: lefjoin

https://stackoverflow.com/questions/45588672/forcing-left-join-to-only-return-one-row-from-matching-ids-in-the-right-table

Anonymous

Left join to only return one row from matching link in the right table11.03.26

Information Epoch 1792726777