Helpers\Table\Db limit on join?

[imJexs]

Let's say I have 3 tables. They are as follows:

• users
• logins
• names

The users table contains an auto incremented primary key as well as a user_unique which is a unique identifier for a user.

The names table contains an auto incremented primary key as well as a name_unique which is a unique name to this table.

Now assume that a user can login with their user_unique BUT they can choose any name they want. So multiple users will end up with the same name_unique.

Inside logins it simple stores users.id (the primary key) and names.id (the primary key) as well as a timestamp.

Here is where my problem lies. I want to show a \Table\Db of the users but also include their last used name from names. So to get this, you would select the login_name from logins ordered by timestamp desc and then take that ID and select name_unique that corresponds to that login. However, whenever I join the logins table, and then join the names table to get their name and show it on the table, it shows ALL of the users' logins and all of the names because it is not limited to only their last login. It joins ALL of their logins including all of the names they've used, including when they've used the same name multiple times.

Is there a way to do a join and only grab the last entry where logins.user_id=users_id?

I really hope this made any sense. It's such a hard thing to get into writing.

[bfarber]

I suggest taking a look at this page: https://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

Yes, it is possible. There are some different approaches that can be taken, but you may want to profile first to see which approach is the most efficient.

[imJexs]

4 hours ago, bfarber said:

I suggest taking a look at this page: https://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

Yes, it is possible. There are some different approaches that can be taken, but you may want to profile first to see which approach is the most efficient.

This doesn't seem to be the exact solution for my particular problem, but it is definitely on the right track. I think I'll actually rework my tables themselves to make them a bit more friendly for this type of work.

While the link did not answer my question specifically, it does answer the general concept of my question so I'll mark it as the answer for future reads.