Question 200617 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – LEFT JOIN only one match per ID

I’m trying to join two tables in a very peculiar way and I can’t find the right query to achieve this.
I want to keep ALL dt1 rows intact and join the first row for each FUND_ID with the respective match from dt2.

See below an example for better understanding

First table: dt1

FUND_ID Name
1 Fund1
1 Fund2
1 Fund8
2 Fund3
2 Fund4

Second table: dt2

FUND_ID Value
1 100
2 200

Desired Join Result:

FUND_ID Name Value
1 Fund1 100
1 Fund2
1 Fund8
2 Fund3 200
2 Fund4

I tried multiple SQL Queries, using LEFT JOIN and DISTINCT, but I was not able to achieve the expected result.
I expect an output table with all the rows from dt1 where the first row for each FUND_ID is joined with the respective match from dt2.

Tags:

2

Answers


  1. 0

    This should work

      SELECT dt1.FUND_ID, dt1.Name, dt2.Value
FROM dt1
LEFT JOIN dt2 ON dt1.FUND_ID = dt2.FUND_ID
AND dt1.Name = (
    SELECT Name
    FROM dt1 dt1_inner
    WHERE dt1_inner.FUND_ID = dt1.FUND_ID
    ORDER BY Name
    LIMIT 1
);
    Login or Signup to reply.
  2. 0

    You can assign a row number to each record within each FUND_ID in your first table using ROW_NUMBER(). Then you can join this with the second table, but only include the value from the second table for the first row of each FUND_ID.

    WITH RankedDt1 AS (
    SELECT 
        FUND_ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY FUND_ID ORDER BY Name) AS RowNum
    FROM
        dt1
)
SELECT
    r.FUND_ID,
    r.Name,
    CASE WHEN r.RowNum = 1 THEN d2.Value END AS Value
FROM
    RankedDt1 r
LEFT JOIN
    dt2 d2
ON
    r.FUND_ID = d2.FUND_ID AND r.RowNum = 1
ORDER BY
    r.FUND_ID, r.RowNum;

    In a few words, rank each row within each FUND_ID in the first table, then join it with the second table, but only bring in the value for the first row in each group.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search