Search code examples
mysqlasp.netasp.net-identity

MySQL Command what does a point mean?

I'm a newbie in mysql and have to write a implemention for a custom mysql asp.net identity storage.

I follow this tutorial and the first steps are done. https://learn.microsoft.com/en-us/aspnet/identity/overview/extensibility/implementing-a-custom-mysql-aspnet-identity-storage-provider Now i have the follow mysql command:

"Select Roles.Name from UserRoles, Roles where UserRoles.UserId = @userId and UserRoles.RoleId = Roles.Id"

My problem is now that i dont know how the table have to look for this request?

I would say: Tablename : Roles Select: Roles and Name? or is it a name? same with UserRoles.UserID and UserRoles.RoleId What does the point mean?

Thanks a lot

Solution

  • You question is quite unclear, however, if I understood correctly, you can't figure out clearly how the database schema you are using is structured and what you'll get from this query.

    The query you have written SELECTs the data field called Name from the table called Roles. In order to do this, the query uses data coming from two tables: one is the Roles table itself, the other is called UserRoles.

    It will extract Names data from the Roles table only for the Roles entries that have the Id field matching with the RoleId field of the entries in the UserRoles table that have the UserId equal to the given @UserId.

    In other words, this SELECT query in will give you as a result a list of Names coming from the entries in the Roles table which match the given conditional check, which is what is written after the where SQL condition: where UserRoles.UserId = @userId and UserRoles.RoleId = Roles.Id.

    Finally, the point "." in SQL queries is used to disambiguate between fields (or columns, if you want to call it so) with same name but coming from different tables. It is quite common that all the tables have an Id field, for example. You can identify the correct Id field in your database by writing Table1.Id, Table2.Id, and so on. Even if you don't have naming conflicts in your tables columns, adding the table name can be very good for code readability.

    Edit: As other users correctly pointed out in the comments to your question, you should also have a look to what an SQL JOIN operation is. Since you are searching data using information coming from different tables, you are actually doing an implicit JOIN on those tables.