"How do I JOIN, THEN WHERE, THEN LIMIT/OFFSET in MySQL?"

ihtus
May 19, 2023
278 views
1 vote
2 Answers

my_tickets

+-----------+---------+
| ticket_id | user_id |
+-----------+---------+
| 186       | 2       |
| 187       | 2       |
| 188       | 2       |
| 253       | 33      |
| 254       | 33      |
| 256       | 33      |
| 261       | 33      |
| 262       | 33      |
| 263       | 33      |
| 1573      | 7       |
| 1597      | 7       |
| 1748      | 7       |
+-----------+---------+

my_users

+----+---------+
| id | name    |
+----+---------+
| 2  | user_2  |
| 7  | user_7  |
| 33 | user_33 |
+----+---------+

my_data

+----+-----------+------------+
| id | ticket_id | data       |
+----+-----------+------------+
| 1  | 186       | data_186_1 |
| 2  | 186       | data_186_2 |
| 3  | 187       | data_187_1 |
| 4  | 253       | data_253_1 |
| 5  | 253       | data_253_2 |
| 6  | 253       | data_253_3 |
| 7  | 254       | data_254_1 |
+----+-----------+------------+

How do I get data (object like data, not rows) for 3 tickets after the tables were joined & only for user_id=33?

I will use this for pagination.

Desired output

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

https://dncrews.com/limit-and-offset-can-work-with-join-f03327fa2ad3

Step 1: JOIN

my_tickets.user_id <=> my_users.id
my_tickets.ticket_id <=> my_data.ticket_id)

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 186                   | 2                   | user_2         | data_186_1    |
| 186                   | 2                   | user_2         | data_186_2    |
| 187                   | 2                   | user_2         | data_187_1    |
| 188                   | 2                   | user_2         | NULL          |
| 1573                  | 7                   | user_7         | NULL          |
| 1597                  | 7                   | user_7         | NULL          |
| 1748                  | 7                   | user_7         | NULL          |
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
| 261                   | 33                  | user_33        | NULL          |
| 262                   | 33                  | user_33        | NULL          |
| 263                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

Step 2: WHERE (my_tickets.user_id = 33)

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
| 261                   | 33                  | user_33        | NULL          |
| 262                   | 33                  | user_33        | NULL          |
| 263                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

Step 3: LIMIT (limit the result to 3 tickets (object kind))

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

What I tried:

Query 1

I expected this query to output 3 results (as in Step 3) after the tables were joined & only for user_id=33, but the output is empty

SELECT * FROM (
    SELECT 
        my_tickets.ticket_id AS my_tickets__ticket_id, my_tickets.user_id AS my_tickets__user_id,
        my_users.name AS my_users__name, my_data.data AS my_data__data
    FROM my_tickets
    
    LEFT JOIN my_users ON my_tickets.user_id=my_users.id 
    LEFT JOIN my_data ON my_tickets.ticket_id=my_data.ticket_id 

    WHERE my_tickets.user_id = 33

) as t1

    WHERE
    
    t1.my_tickets__ticket_id IN (
        SELECT * FROM (
            SELECT ticket_id FROM my_tickets LIMIT 3 OFFSET 0
        ) as t2
    )

Output: Empty table

Query 2

SELECT * FROM (
    SELECT 
        my_tickets.ticket_id AS my_tickets__ticket_id, my_tickets.user_id AS my_tickets__user_id,
        my_users.name AS my_users__name, my_data.data AS my_data__data
    FROM my_tickets
    
    LEFT JOIN my_users ON my_tickets.user_id=my_users.id 
    LEFT JOIN my_data ON my_tickets.ticket_id=my_data.ticket_id 

    WHERE my_tickets.user_id = 33

) as t1

Output:

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
| 261                   | 33                  | user_33        | NULL          |
| 262                   | 33                  | user_33        | NULL          |
| 263                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

Query 3

SELECT * FROM (
    SELECT 
        my_tickets.ticket_id AS my_tickets__ticket_id, my_tickets.user_id AS my_tickets__user_id,
        my_users.name AS my_users__name, my_data.data AS my_data__data
    FROM my_tickets
    
    LEFT JOIN my_users ON my_tickets.user_id=my_users.id 
    LEFT JOIN my_data ON my_tickets.ticket_id=my_data.ticket_id

) as t1

    WHERE
    
    t1.my_tickets__ticket_id IN (
        SELECT * FROM (
            SELECT ticket_id FROM my_tickets LIMIT 3 OFFSET 0
        ) as t2
    )

Output:

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 186                   | 2                   | user_2         | data_186_1    |
| 186                   | 2                   | user_2         | data_186_2    |
| 187                   | 2                   | user_2         | data_187_1    |
| 188                   | 2                   | user_2         | NULL          |
+-----------------------+---------------------+----------------+---------------+

I complicated things by using subquery for a reason. I need to use LIMIT OFFSET for pagination of the content. And MySQL is applying the LIMIT on the number of rows returned. But I need to apply LIMIT on the object-like data because I updated the tables in the initial question to reflect that (added my_data table) based on article https://dncrews.com/limit-and-offset-can-work-with-join-f03327fa2ad3 .

Answers

- lemon
- May 18, 2023 at 2:12 pm
- 0 votes
0
You seem to be overcomplicating things. You can do it without subqueries:
```
SELECT t.ticket_id,
       t.user_id,
       u.name
FROM       mytickets t
INNER JOIN myusers u ON t.user_id = u.id
WHERE user_id = 33
LIMIT 3
```
This holds the correct result because the following activate in the given order:
1. FROM clause
2. WHERE clause
3. SELECT clause
4. LIMIT clause
Here you can get a thorough discussion on execution order among clauses, or you can go on the official documentation.

Additionally, note that the output is not deterministic, as you don’t know the order in which data is stored inside the database. The only way to retrieve your three rows deterministically is by using an ORDER BY clause, able to impose an order among your rows and break potential ties.

Output:

ticket_id user_id name

253 33 user_33

254 33 user_33

256 33 user_33

Check the demo here.

Update: What if I want to limit object-like data to a specified amount?

Don’t use LIMIT, as it brings unnecessary complexity and it becomes very difficult to handle.

You can instead apply filtering over the output of DENSE_RANK ranking window function. It will assign an identifier to each record of your object-like rows, which you can apply filtering on.

Here’s an example:
```
WITH cte AS (
    SELECT t.ticket_id,
           t.user_id,
           u.name,
           DENSE_RANK() OVER(ORDER BY user_id) AS rn
    FROM       mytickets t
    INNER JOIN myusers u ON t.user_id = u.id
)
SELECT * 
FROM cte
WHERE rn <= 3
```
This query will allow you to pick all records from the last three user_id values, which is not the last three records only.
Login or Signup to reply.

- PSalmon
- May 19, 2023 at 8:21 am
- 0 votes
0
First get distinct users and tickets limiting to 3 then join
```
select s.*,md.*,mu.name from
(select distinct user_id,ticket_id from my_tickets mt  where user_id = 33 limit 3) s
left join my_data md on md.ticket_id = s.ticket_id
join my_users mu on mu.id = s.user_id
```
https://dbfiddle.uk/eloidh90
Login or Signup to reply.