two tables single query cannot execute?

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

Answer 1

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 

Answer 2

User-1026236167 posted

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

Answer 3

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

Answer 4

User-939850651 posted

Hi prabhjot1313,

Have your problem solved?