CODEWITHSUNDEEP
mysqlsqljoinleft-joinrow-number
Gaurav Gandhi
Gaurav Gandhi

Reputation: 3201

Left outer join? Row numbering issue

I have three tables, one table having subject, one table for user data, and one table for storing user subscription.

A user may/not be subscribed its optional, so to get all user list with their subscription status, I use left join as

SET @rnum = 0;

SELECT (@rnum :=@rnum + 1) AS rn, user.user_name, subs.status 
FROM user_tbl LEFT JOIN subs_tbl ON user.uid = subs.uid LIMIT 0, 10;

I was using output of this as whole at a time, but now as data has grown high, I have implemented paging of this output in my application.

For paging I created a row_num mechanism, I will store the last row_num displayed and on next page will display from last_num.

Now I want sorting of user with subscribed first or last, so I used

SELECT (@rnum :=@rnum + 1) AS rn, user.user_name, subs.status
FROM user_tbl LEFT JOIN subs_tbl ON user.uid = subs.uid ORDER BY subs.status LIMIT 0, 10;

which is giving row numbers but not as I wanted, it gives row number first then do sorting, so I don't get result as desired.

So I need help on following,

  1. Is any better way of paging ?
  2. Is there any substitue of Left Join (I want null values also, because I want all user info).
  3. Is there any other method of numbering the SQL result.
  4. Is there any PHP regarding function or anything that can help me in this case.

[ This is sample created from my actual problem, in my actual problem, I have a huge query with multiple joins and sub_queries, so nesting of this query and numbering it is not possible ]

Upvotes: 3

Views: 405

Answers (1)

Andriy M
Andriy M

Reputation: 77687

Perhaps you are looking for something like this:

(Removed as not meeting the OP's requirements, as reminded by a comment.)

Alternatively, you could also try parametrisation of LIMIT. As of MySQL 5.0.7, LIMIT is parametrisable when used in a statement that is part of a stored procedure or a prepared statement:

PREPARE stmt FROM
'SELECT
...
LIMIT ?, ?';

EXECUTE stmt USING @offset, @limit;

It's worth noting that for this method it would make most sense to have as unambiguous sorting order as possible.

Upvotes: 2

Related Questions