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