SQL LIMIT by id column

Question

I have the following statement:

SELECT s.*, u.`fname`, u.`lname`, aif.`airport`
FROM services s
INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id`
INNER JOIN users u
    ON s.`service_provider_id` = u.`id`

Lets say for example that this is the result I am getting:

id | ----- | ------ | ...............
 1 |  Jack |  Jones | ...............
 1 |  Moses|  Cohen | ...............
 2 |  Tom  |  Jones | ...............
 3 |  Luke | SkyWal | ...............

So I need to use LIMIT 2 so that I will get the first 3 rows (and not only the first 2). So the Limit should be based on the id column. It should work also when using OFFSET.

How can this be done?

Answer 1

Instead of reading the services table directly, use a sub query and join to that.

select s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
    ( select * from services order by id limit 2) as s INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id` INNER JOIN users u
    ON s.`service_provider_id` = u.`id`

Depending on your version of mysql, you can't have a subquery IN using limit and offset (This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery') but this will still work.

EDIT:

If you set the inner query to this:

(SELECT s.id
FROM services s
INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id`
INNER JOIN users u
    ON s.`service_provider_id` = u.`id`
LIMIT 2)

Then it will only return services with airports_in_flight and users (possibly add a distinct clause).

EDIT to clarify:

Right now you have this as your select:

select s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
        services as s INNER JOIN airports_in_flight aif
        ON s.`id` = aif.`service_id` INNER JOIN users u
        ON s.`service_provider_id` = u.`id`

You want to limit to 2 services (I don't know if you want all services, I'm guessing just the ones with matching users and airports in flight), so you need to put the limit on the right table.

In this case, the right table is the services table. To make things simple lets start with a simplified version of what you have:

SELECT s.*, aif.`airport` FROM 
        services as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

I'm going to make an assumption that there is an airport in flight row for every service (we can add that complexity later).

To list the first 2 services, we want the limit on the services, not the whole query, so it would be:

SELECT s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
        (select * from services limit 2) as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

Notice I replaced the table services with a query, that I can now limit, this is the subquery. If we now want to only look at services that have an airport in flight, we need to change that subquery from:

select * from services limit 2

to

select ss.* from services ss 
inner join airports_in_flight aifs on ss.`in` = aifs.`service_id` limit 2

I've renamed the services and airports_in_flight tables ss and aifs so they don't collide with the names in the main query, put an inner join in to limit my rows to only service table, and limited by 2, so putting the subquery into the query we now get:

select s.*, u.`fname`, u.`lname`, aif.`airport` 
FROM 
  (select ss.* from services ss 
   inner join airports_in_flight aifs on ss.`in` = aifs.`service_id`
   limit 2) as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

You should be able to then expand the subquery to add the inner join for users (thus limiting services to only those that have airports_in_flight rows and users rows), and add the users table to the main query.