MySQL two-table INNER JOIN , LEFT JOINED onto third table with only one row with lowest value

Question

I searched around and found a near example to what I'm looking for, but it doesn't work in my case.

I have a query that does an INNER JOIN on two tables and this join constrains my overall data set substantially. I then want to LEFT JOIN onto a third table but I only want one record from that third table. The reason for the left join is because not every result of the INNER JOIN has a match in the 3rd table. Something like this:

SELECT DISTINCT t1.code, t2.id, t2.code, t3.id, t3.source_title, t3.display_order
FROM table1 t1
INNER JOIN table2 t2 ON t2.code=t1.code AND t2.type=0
LEFT JOIN table3 t3 ON t3.code=t1.code
ORDER BY t1.code, t3.display_order

This query returns too many records because the third table contains multiple records with a matching code. I just want the first one that matches with the lowest display_order value and, unfortunately, I can't limit the records to have display_order=1 because the lowest display order is not always one.

IMPORTANT: The t3.id value (if any) returned by this query must correspond to the record with the lowest display_order value. I.e., it won't work if the query correctly returns the lowest display_order value but the t3.id value corresponds to some other record in table 3.

Is this even possible? Any help would be much appreciated.

EDIT: Per Nick's suggestion, I have tried this, which appears to be working. I'll do some verification and report back:

SELECT DISTINCT t1.code, t2.*, sq.id, sq.source_title, sq.display_order
FROM table1 t1
INNER JOIN table2 p ON t2.code=t1.code AND t2.type=0
LEFT JOIN (
    SELECT t3.*
    FROM table3 t3
    WHERE t3.display_order=(
        SELECT MIN(display_order)
        FROM table3 t3a 
        WHERE t3a.code = t3.code
    )
) sq ON sq.code=t1.code
ORDER BY t1.code, sq.display_order

Could you provide some sample data and expect result? – D-Shih Sep 18 '18 at 23:01 — D-Shih, Sep 18 '18 at 23:01
@D-Shih it'll take me some time. the data is proprietary. – S. Imp Sep 18 '18 at 23:07 — S. Imp, Sep 18 '18 at 23:07

score 1 · Accepted Answer · answered Sep 18 '18 at 23:03

1

You should be able to replace table3 in your LEFT JOIN with

(SELECT * 
 FROM table3 t3 
 WHERE display_order = (SELECT MIN(display_order) 
                        FROM table3 t3a 
                        WHERE t3a.code = t3.code)
) t3

answered Sep 18 '18 at 23:03

Nick

138,499
22
57
95

sticky bit · Answer 2 · 2018-09-19T01:18:33.507

In MySQL 8.0 you can try to use row_number() for each code and ordered by display_order in a subquery from table3. Then left join that result and check for the row_number() to be equal to 1.

SELECT DISTINCT
       t1.code,
       t2.id,
       t2.code,
       t3.id,
       t3.source_title,
       t3.display_order
       FROM table1 t1
            INNER JOIN table2 t2
                       ON t2.code = t1.code
            LEFT JOIN (SELECT t3.id,
                              t3.source_title,
                              t3.display_order,
                              t3.code,
                              row_number() OVER (PARTITION BY t3.code
                                                 ORDER BY t3.display_order) rn
                              FROM table3 t3) t3
                      ON t3.code = t1.code
       WHERE t2.type = 0
             AND t3.rn = 1
       ORDER BY t1.code,
                t3.display_order;

In lower versions you can try correlated subqueries ordered by display_order and LIMIT 1 (to get only one record).

SELECT DISTINCT
       t1.code,
       t2.id,
       t2.code,
       (SELECT t3.id
               FROM table3 t3
               WHERE t3.code = t1.code
               ORDER BY t3.display_order,
                        t3.id
               LIMIT 1) id,
       (SELECT t3.source_title
               FROM table3 t3
               WHERE t3.code = t1.code
               ORDER BY t3.display_order,
                        t3.id
               LIMIT 1) source_title,
       (SELECT t3.display_order
               FROM table3 t3
               WHERE t3.code = t1.code
               ORDER BY t3.display_order,
                        t3.id
               LIMIT 1) display_order
       FROM table1 t1
            INNER JOIN table2 t2
                       ON t2.code = t1.code
       WHERE t2.type = 0
       ORDER BY t1.code,
                (SELECT t3.display_order
                        FROM table3 t3
                        WHERE t3.code = t1.code
                              ORDER BY t3.display_order,
                                       t3.id
                              LIMIT 1);

I assumed, that display_order in table3 isn't unique but id is. So I added id to the ORDER BY clauses in the subqueries to make sure the same record is selected in each of them. If display_order is unique, you can remove id FROM the ORDER BY clauses.

Edit:

If you don't want to repeat the subqueries in the (overall) ORDER BY clause, you can also order by the column ordinals. E.g.:

...
ORDER BY 1, 6;

My workstation (which I plan to upgrade shortly) has a much older version, **5.6.33**. I'll see if I can get this other query to work -- although that's pretty verbose. — S. Imp, Sep 19 '18 at 01:03
I want to say that second offering you have (which is a work of art) does provide the exact data set I'm after, but I can't seem to work with it much (e.g., change the overall sort order). — S. Imp, Sep 19 '18 at 01:10
@S.Imp: Not a 100% sure what the problem is, but I made a guess. Maybe my edit helps? — sticky bit, Sep 19 '18 at 01:19
Aha! I've got it sorting, etc. That said, it's a bit verbose/cumbersome. I've edited my original post with a somewhat more compact solution. — S. Imp, Sep 19 '18 at 01:39

MySQL two-table INNER JOIN , LEFT JOINED onto third table with only one row with lowest value

2 Answers2