MySQL - How to first JOIN then do WHERE then LIMIT OFFSET

Question

For simplicity: having these 3 tables

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 |
+----+-----------+------------+

WHAT I NEED: 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          |
+-----------------------+---------------------+----------------+---------------+

Please read this article - https://dncrews.com/limit-and-offset-can-work-with-join-f03327fa2ad3

STEPS:

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 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

UPDATE: Answering lemon's statement: I over-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, on the other hand I need to apply LIMIT on the object-like data. Why object like data? => I updated the tables in initial question to reflect that (added my_data table) Based on this article - https://dncrews.com/limit-and-offset-can-work-with-join-f03327fa2ad3

Please put all & only what is needed to ask in your post, Quote with credit & relate to your post. Don't expect readers to read entire other pages or guess what is relevant & why. Please use code format for code. Please don't YELL. — philipxy, May 19 '23 at 01:30
This post lines up a where with its select/from, yet indents other wheres from their select/from. Please pick one style. PS Just vertically aligning query clause keywords (and usually ONs) minimizes indentation. Indenting clauses of a query doesn't add clarification & makes it harder to find the clauses that form a query. — philipxy, May 19 '23 at 06:09
This doesn't clearly say how output is a function of input. Don't expect us to guess from 1 (too big) example. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS If you do not say what you expect & why & justify per competent documentation, you ask us to (re)write yet another presentation of the language, with no details on what you misunderstand or do or don't understand. — philipxy, May 19 '23 at 06:22
Queries return rows. What do you mean by *object like data, not rows*? — forpas, May 19 '23 at 11:30
@forpas: for user_id=33 would like to get 5 rows (see "Desired output"). Those 5 rows are actually 3 object elements (for ticket_id: 253, 254, 256) — ihtus, May 19 '23 at 11:46

lemon · Answer 1 · 2023-05-18T17:30:36.903

3

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:

FROM clause
WHERE clause
SELECT clause
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.

edited May 18 '23 at 17:30

answered May 18 '23 at 12:12

lemon

14,875
6
18
38

There's no real sequence of steps in SQL since it's a declarative language. However, that explanation can be useful as a conceptual idea of how it could work. – The Impaler May 18 '23 at 13:38
@lemon I appreciate your time answering my question. However I didn't describe well my case. I just updated the post with more details. Kindly please check it. Thank you – ihtus May 18 '23 at 17:11
In order to solve that problem, you can avoid using the `LIMIT ... OFFSET` clause, and use a `DENSE_RANK` window function, that assigns a ranking value to each object you need. Then, after having stored the output inside a subquery, a `WHERE` clause will help you filter out the amount of "objects" you don't need. – lemon May 18 '23 at 17:16
@lemon - interesting idea! Thanks! I will look into this, never used it. It would help a lot if you can update your fiddle with DENSE_RANK. – ihtus May 18 '23 at 17:20
1

I'm still not sure how the example in your post would fit the `LIMIT OFFSET` idea, but I've updated my answer with an example of what I meant. – lemon May 18 '23 at 17:29
@lemon thanks! Is it possible to simulate OFFSET functionality with DENSE_RANK ? – ihtus May 18 '23 at 17:30
1

Yes, that's what I explain in the updated answer. – lemon May 18 '23 at 17:30
@lemon: looks like CTE is not available in MySQL 5.7... – ihtus May 18 '23 at 17:48
I'd strongly recommend to update your version if you can. There are lots of limitations with legacy versions of MySQL, including `LIMIT ... OFFSET` as the only possible way, with some complex workarounds involving either variables or dynamic queries. – lemon May 18 '23 at 17:51
@lemon is there a way to use DENSE_RANK without CTE? – ihtus May 18 '23 at 17:53
DENSE_RANK is not available in MySQL 5.7, you shall go through a `LIMIT OFFSET` solution is that is the environment. – lemon May 18 '23 at 17:53
@lemon: can't upgrade to 8, so I am stuck with 5.7. Would highly appreciate if you can suggest a solution using LIMIT OFFSET clause.. – ihtus May 18 '23 at 17:57

score 1 · Accepted Answer · answered May 19 '23 at 06:21

1

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

answered May 19 '23 at 06:21

P.Salmon

17,104
2
12
19

thanks for the idea [first do WHERE (my_tickets) => then LIMIT (my_tickets) => then JOIN (other tables)] I applied that to my real life tables and looks like it works! – ihtus May 19 '23 at 14:46

MySQL - How to first JOIN then do WHERE then LIMIT OFFSET

2 Answers2