C# WPF SQL SELECT INNER JOIN

Question

Im trying to join a user table to retrieve the users login name. I wish to have TWO INNER joins one for CreatedByUser_loginname and ModifiedByUser_loginname

But at the moment Im just trying to get the SQL query string syntax right. BUT, When I change the name of the INNER JOIN with an AS 'name' I get an exception thrown when sqlreader() method is executed. If I remove the AS ... It works, but then I'm unable to add the second INNER JOIN to the same User table to get the name of the modifiedbyuser login name.

Here's the offending code

This doesnt work

String sqlComStr = "SELECT Company_ID, Company_Name, Company_Address1, Company_Address2, Company_Address3, Company_Suburb, Company_City, " +
    "Company_State, Company_PostCode, Company_Phone, Company_Fax, Company_WebsiteUrl, Company_Status, Company_ModifiedByUser_ID, Company_CreatedByUser_ID, " +
    " [U1].User_LoginName " +
    "FROM Company INNER JOIN [User] AS U1 ON [User].User_ID = [Company].Company_CreatedByUser_ID WHERE [Company].isDeleted = 0" + companyFilter;

This does

String sqlComStr = "SELECT Company_ID, Company_Name, Company_Address1, Company_Address2, Company_Address3, Company_Suburb, Company_City, " +
    "Company_State, Company_PostCode, Company_Phone, Company_Fax, Company_WebsiteUrl, Company_Status, Company_ModifiedByUser_ID, Company_CreatedByUser_ID, " +
    " User_LoginName " +
    "FROM Company INNER JOIN [User] ON [User].User_ID = [Company].Company_CreatedByUser_ID WHERE [Company].isDeleted = 0" + companyFilter;

....

String sqlComStr = "SELECT Company_ID, Company_Name, Company_Address1,    Company_Address2, Company_Address3, Company_Suburb, Company_City, " +
    "Company_State, Company_PostCode, Company_Phone, Company_Fax, Company_WebsiteUrl, Company_Status, Company_ModifiedByUser_ID, Company_CreatedByUser_ID, " +
    " [U1].User_LoginName " +
    "FROM Company INNER JOIN [User] AS U1 ON [User].User_ID = [Company].Company_CreatedByUser_ID WHERE [Company].isDeleted = 0" + companyFilter;

sqlcmd.CommandText = sqlComStr;
sqlCon.ConnectionString = "Data Source='PTSSHM\\SQLEXPRESS'; Initial Catalog='PROACTIVE'; Integrated Security=True;";
//sqlCon.ConnectionString = App.appSqlConnectionString;
sqlcmd.Connection = sqlCon;

try
{
    sqlCon.Open();

    SqlDataReader sqlReader = sqlcmd.ExecuteReader(CommandBehavior.Default);
    // If no Exception thrown at this point clear the quote items
    companys.Clear();

    while (sqlReader.Read())
    // Read each row and create a new quite item object int he quoteitems list
    {
        companys.Add(new Company((int)sqlReader["Company_ID"],
            (string)sqlReader["Company_Name"].ToString(),
            (string)sqlReader["Company_Address1"].ToString(),
            (string)sqlReader["Company_Address2"].ToString(), 
            (string)sqlReader["Company_Address3"].ToString(),
            (string)sqlReader["Company_Suburb"].ToString(),
            (string)sqlReader["Company_City"].ToString(),
            (string)sqlReader["Company_State"].ToString(),       
            (string)sqlReader["Company_PostCode"].ToString(),
            (string)sqlReader["Company_Phone"].ToString(),
            (string)sqlReader["Company_Fax"].ToString(),
            (string)sqlReader["Company_WebsiteUrl"].ToString(),
            (string)sqlReader["Company_Status"].ToString(),
            (int)sqlReader["Company_ModifiedByUser_ID"],
            (int)sqlReader["Company_CreatedByUser_ID"],
            (string)sqlReader["[U1].User_LoginName"].ToString(), "d"));
    }
}

Answer 1

If you use an alias for a table name such as User as U1, then the name [user] no longer exists for the query, you must use U1 on all of the subsequent references to it.

INNER JOIN [User] AS U1 ON [User].User_ID = [Company].Company_CreatedByUser_ID

You are still using User in the join clause,

INNER JOIN [User] AS U1 ON U1.User_ID = [Company].Company_CreatedByUser_ID

Answer 2

Fixed

String sqlComStr = "SELECT Company_ID, Company_Name, Company_Address1, Company_Address2, Company_Address3, Company_Suburb, Company_City, " +
            "Company_State, Company_PostCode, Company_Phone, Company_Fax, Company_WebsiteUrl, Company_Status, Company_ModifiedByUser_ID, Company_CreatedByUser_ID, " +
            " [CreateUser].User_LoginName AS CreateUser, [ModUser].User_LoginName AS ModUser  " +
            "FROM Company INNER JOIN [User] As [CreateUser] ON [CreateUser].User_ID = [Company].Company_CreatedByUser_ID INNER JOIN [User] AS [ModUser] ON [ModUser].User_ID = [Company].Company_ModifiedByUser_ID WHERE [Company].isDeleted = 0" + companyFilter;

and

companys.Add(new Company((int)sqlReader["Company_ID"], (string)sqlReader["Company_Name"].ToString(), (string)sqlReader["Company_Address1"].ToString(),
                    (string)sqlReader["Company_Address2"].ToString(), (string)sqlReader["Company_Address3"].ToString(), (string)sqlReader["Company_Suburb"].ToString(),
                    (string)sqlReader["Company_City"].ToString(), (string)sqlReader["Company_State"].ToString(), (string)sqlReader["Company_PostCode"].ToString(),
                    (string)sqlReader["Company_Phone"].ToString(), (string)sqlReader["Company_Fax"].ToString(), (string)sqlReader["Company_WebsiteUrl"].ToString(),
                    (string)sqlReader["Company_Status"].ToString(), (int)sqlReader["Company_ModifiedByUser_ID"], (int)sqlReader["Company_CreatedByUser_ID"],
                    (string)sqlReader["CreateUser"].ToString(), (string)sqlReader["ModUser"].ToString()));