locked
SQL Statement issue - ¿Why? RRS feed

  • Question

  • User-1595992518 posted

    Hi all!

     This is my first post in this forum. I've read many times before I decided to post because my poor english, so please foregive me if there is too many errors.

    ---

     I'm trying to write a SQL statement against the ASPNETDB.MDF to retrieve the users and his/her roles. What I need is:

    LoweredUserName FROM aspnet_Users 

    LoweredEmail FROM aspnet_Membership

    RoleName FROM aspnet_Roles

    * Users only have one Role

    So I've tried this:

    SELECT aspnet_Users.UserName, aspnet_Membership.Email, aspnet_Roles.RoleName
    FROM    aspnet_Roles INNER JOIN
                  aspnet_Membership INNER JOIN
                  aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN
                  aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId ON 
                  aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId

    But it doesn't work and i don´t understand beacuse it does work in Access database.

    Can anyone help me to understand what I've doing wrong? Is there any other way to make what i need?

    Please help!!!

    Monday, February 16, 2009 4:26 PM

Answers

  • User-1595992518 posted

    Hi hans_v!

    I've make a mistake posting this message in Access forum. I wanted to post in SQL Server dedicated forum.

    LCASE not is the problem. The problem is that the query analyzer reports nothing in SQL Server 2005.

     Thanks a lot.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 16, 2009 5:43 PM

All replies

  • User1264447444 posted

    SELECT LOWER(aspnet_Users.UserName),LOWER(aspnet_Membership.Email),LOWER(aspnet_Roles.RoleName)
    FROM    aspnet_Users
           JOIN aspnet_Membership ON aspnet_Membership.UserId = aspnet_Users.UserId
           JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId


     
    Monday, February 16, 2009 4:55 PM
  • User-1595992518 posted

    Hi inquisitive_mind, thank you for you reply but it does not work. I don´t see aspnet_Roles table in your join query and the query analyzer throw an error.

    What I fail to understand why my query works in Access but not in SQL Server 2005 (express ed.) 

    Monday, February 16, 2009 5:15 PM
  • User1264447444 posted

    Sorry my bad

    SELECT LOWER(aspnet_Users.UserName),
           LOWER(aspnet_Membership.Email),
           LOWER(aspnet_Roles.RoleName)
    FROM   aspnet_Users 
    JOIN aspnet_Membership ON aspnet_Membership.UserId = aspnet_Users.UserId 
    JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId 
    JOIN aspnet_Roles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId
     
    Monday, February 16, 2009 5:27 PM
  • User-1199946673 posted

    I'm not exactly sure what the problem is. you're talking about ASPNETDB.MDF, and also about Access... So which database are you using?

    The reason that a SQL statement is working in SQL Server and not in Access or vice versa, is because there're slight differences in the syntax. For example the equivalent of the SQL Server LOWER function in Access is LCase

    For a list of main differences, refer to:

    http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

     

    Monday, February 16, 2009 5:30 PM
  • User-1595992518 posted

    Hi again!

    I've tried what you wrote but it does not work in SQL Server 2005 (it does work in Access database).

     Really, I'm going crazy.

     Thank you for your interest and support.

     

    Monday, February 16, 2009 5:37 PM
  • User-1595992518 posted

    Hi hans_v!

    I've make a mistake posting this message in Access forum. I wanted to post in SQL Server dedicated forum.

    LCASE not is the problem. The problem is that the query analyzer reports nothing in SQL Server 2005.

     Thanks a lot.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 16, 2009 5:43 PM