locked
two tables single query cannot execute? RRS feed

  • Question

  • User-1026236167 posted

    hello 

    i have two tables both tables have user_id column my requirement is when tbl_employee user_id is apply then they can login and if tbl_master_party user_id is login then they can login

    i have to apply this query in visual studio aspx but an error occur ambigious column name 

    how they can execute 

    here is my query

    select tbl_master_party.user_id, tbl_employee.user_id
    from tbl_master_party, tbl_employee where tbl_master_party.tbl_id = tbl_master_party.tbl_id and tbl_employee.tbl_id = tbl_employee.tbl_id

    Wednesday, August 12, 2020 4:07 PM

Answers

  • User-939850651 posted

    Hi prabhjot1313,

    I created a simple example (if the data table structure is similar), the query statement you provided did not produce the error.

    And the columns you query clearly specify the source of the table.

    create table tbl_employee(
    [user_id] int primary key identity(1,1),
    [tbl_id] int default 1,
    [employee_name] varchar(50)
    )
    
    insert into tbl_employee (employee_name) values ('employee_name1'),('employee_name2'),('employee_name3')
    create table tbl_master_party(
    [user_id] int primary key identity(1,1),
    [tbl_id] int default 2,
    [master_name] varchar(50)
    )
    
    insert into tbl_master_party (master_name) values ('master_name1'),('master_name2'),('master_name3')
    
    DataTable dt = new DataTable();
                string constr = ConfigurationManager.ConnectionStrings["conStr"].ToString();
                using (SqlConnection conn = new SqlConnection(constr)) {
                    string query = "select tbl_master_party.user_id, tbl_employee.user_id " +
                        "from tbl_master_party, tbl_employee " +
                        "where tbl_master_party.tbl_id = tbl_master_party.tbl_id and tbl_employee.tbl_id = tbl_employee.tbl_id";
                    using (SqlCommand cmd = new SqlCommand(query, conn)) {
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                    }
                }

    Result:

    If I misunderstood what you mean, could you provide more details?

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 20, 2020 3:42 AM

All replies

  • User475983607 posted

    The T-SQL makes no logical sense but you can use an alias.

    SELECT tbl_master_party.user_id as MasterUserId, 
           tbl_employee.user_id as EmployeeUserId
    FROM   tbl_master_party, 
           tbl_employee 
    WHERE  tbl_master_party.tbl_id = tbl_master_party.tbl_id 
           AND tbl_employee.tbl_id = tbl_employee.tbl_id 

    Wednesday, August 12, 2020 4:17 PM
  • User-1026236167 posted

    sir this query can repeat same error ambigious column name when i apply this query

    Thursday, August 13, 2020 5:35 AM
  • User-939850651 posted

    Hi prabhjot1313,

    I created a simple example (if the data table structure is similar), the query statement you provided did not produce the error.

    And the columns you query clearly specify the source of the table.

    create table tbl_employee(
    [user_id] int primary key identity(1,1),
    [tbl_id] int default 1,
    [employee_name] varchar(50)
    )
    
    insert into tbl_employee (employee_name) values ('employee_name1'),('employee_name2'),('employee_name3')
    create table tbl_master_party(
    [user_id] int primary key identity(1,1),
    [tbl_id] int default 2,
    [master_name] varchar(50)
    )
    
    insert into tbl_master_party (master_name) values ('master_name1'),('master_name2'),('master_name3')
    
    DataTable dt = new DataTable();
                string constr = ConfigurationManager.ConnectionStrings["conStr"].ToString();
                using (SqlConnection conn = new SqlConnection(constr)) {
                    string query = "select tbl_master_party.user_id, tbl_employee.user_id " +
                        "from tbl_master_party, tbl_employee " +
                        "where tbl_master_party.tbl_id = tbl_master_party.tbl_id and tbl_employee.tbl_id = tbl_employee.tbl_id";
                    using (SqlCommand cmd = new SqlCommand(query, conn)) {
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                    }
                }

    Result:

    If I misunderstood what you mean, could you provide more details?

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 20, 2020 3:42 AM
  • User-939850651 posted

    Hi prabhjot1313,

    Have your problem solved?

    Thursday, August 27, 2020 3:51 AM