How to join on _first_ record?

Question

With my limited knowledge of complex mysql queries I'm trying to retrieve some information from a database.

The story is this; users get an invite to come to our company. On the basis of this one invite, users can get multiple notifications and multiple appointments. I've got three relevant tables:

invites
-------
| id | name    | created    |
-----------------------------
|  1 | someth1 | 2018-02-03 |
|  2 | someth2 | 2018-02-03 |
|  3 | someth3 | 2018-02-03 |

notifications
-------------
| id | inv_id  | message |
--------------------------
|  1 | 101     | hello   |
|  2 | 287     | hi      |
|  3 | 827     | hey     |

appointments
------------
| id | inv_id  | start_at   |
-----------------------------
|  1 | 101     | 2018-02-03 |
|  2 | 287     | 2018-02-08 |
|  3 | 827     | 2018-02-15 |

I currently have a query, which shows a list of notifications send to users, for all invites done after 1 feb 2018, and which have an appointment no later than '2018-03-10'.

SELECT id, inv_id, message
FROM notifications 
WHERE inv_id IN (
    SELECT id
    FROM invites as invite
    WHERE created > '2018-02-01'
    AND id IN (
        SELECT inv_id 
        FROM appointments
        WHERE invite.id = inv_id
        AND start_at < '2018-03-10'
    )
)
ORDER BY inv_id ASC;

The result looks something like this:

| id | inv_id  | message |
--------------------------
|  1 | 101     | hello   |
|  2 | 287     | hi      |
|  3 | 827     | hey     |

I now want to add the start_at of the first appointment for these notifications

| id | inv_id  | message | start_at   |
---------------------------------------
|  1 | 101     | hello   | 2018-02-03 |
|  2 | 287     | hi      | 2018-02-08 |
|  3 | 827     | hey     | 2018-02-15 |

But from here I'm kinda lost in how I should do that.

Does anybody know how I can add the start_at of the first appointment which corresponds to the invite for the respective notification? So it should show the start_at of the first appointment for the invite of the notification inv_id?

Answer 1

You don't need a lot of subqueries to get the desired result. Just good use of join operations and just one subquery to get the first appointment so here is the query you need:

select n.id, n.inv_id, n.message, a.startat as start_at
  from invites i
        inner join notifications n
               on i.id = n.inv_id
        inner join (select inv_id, min(start_at) startat
                      from appointments 
                     where start_at < '2018-03-10'
                     group by inv_id) a
               on n.inv_id = a.inv_id
 where i.created > '2018-02-01';

Notice that for your current sample data your desired result is impossible since in your query you use the notifications.inv_id IN invites.id and there is no equivalence (1,2,3 are different from 101, 287, 827).

Because of that I created a SQLFiddle to show the working query but with those ids "101, 287, 827" as invites. Here it is: http://sqlfiddle.com/#!9/2d5175/2

Also, if you want the notifications even if there is no appointment for it change the join operation between notifications and the subquery from inner join to left join

Answer 2

Try this:

SELECT id, N.inv_id, message,A.start_at
FROM notifications N
JOIN(
    SELECT inv_id,MIN(start_at) start_at 
    FROM appointments 
    WHERE inv_id IN (
                SELECT id
                FROM invites as invite
                WHERE created > '2018-02-01'
                AND id IN (
                    SELECT inv_id 
                    FROM appointments
                    WHERE invite.id = inv_id
                    AND start_at < '2018-03-10'
                )
            )
    GROUP BY inv_id
    )A ON N.inv_id = A.inv_id
WHERE inv_id IN (
    SELECT id
    FROM invites as invite
    WHERE created > '2018-02-01'
    AND id IN (
        SELECT inv_id 
        FROM appointments
        WHERE invite.id = inv_id
        AND start_at < '2018-03-10'
    )
)
ORDER BY inv_id ASC;