CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Rishav Ranjan
Rishav Ranjan

Reputation: 15

I have already specified different table names to avoid ambiguity still error exists

With tbl_name as 
     (Select top(1000) * 
      from tblEmployee as E right join tblTransaction as T
      on E.EmployeeNumber = T.EmployeeNumber
      where E.EmployeeNumber is null
      order by T.EmployeeNumber)
Select Distinct(T.EmployeeNumber) as EmployeeNum from tbl_name

The error which I am getting:-

Msg 8156, Level 16, State 1, Line 24 The column 'EmployeeNumber' was specified multiple times for 'tbl_name'.

Upvotes: 1

Views: 110

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

It is entirely unclear how an employee number in tblTransaction would not exist. If you had properly declared foreign key relationships, this could not happen.

You can use the structure of your query, but I would recommend LEFT JOIN:

with tbl_name as (
      select top (1000) T.EmployeeNumber
      from tblTransaction T left join
           tblEmployee E
           on E.EmployeeNumber = T.EmployeeNumber
      where E.EmployeeNumber is null
      order by T.EmployeeNumber
     )
Select Distinct EmployeeNumber
from tbl_name;

Your query has several issues:

  • The select * is doing exactly what the error message suggests. There are multiple columns with the same name.
  • The T in the outer query's reference to T.EmployeeNumber is not defined.
  • You are using parentheses around DISTINCT, as if it were a function. It is not. There is a syntactic element in SQL that is SELECT DISTINCT.

I'm not sure if you really need the TOP. NOT EXISTS is a more natural way to write the query:

select distinct t.EmployeeNumber
from tblTransaction T 
where not exists (select 1
                  from tblEmployee E
                  where E.EmployeeNumber = T.EmployeeNumber
                 );

Upvotes: 4

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

The issue is that you have the same column in both the tables you have in the Join. instead of SELECT TOP(1000) * specify the column names from the proper table. if 2 tables are having the same column name and you need both of them in the result set, please give one of the columns a different alias name. something like this

WITH tbl_name
     AS (SELECT TOP (1000) 
            E_EmployeeNumber = E.EmployeeNumber ,
            T_EmployeeNumber = T.EmployeeNumber
         FROM tblEmployee AS E
              RIGHT JOIN tblTransaction AS T ON E.EmployeeNumber = T.EmployeeNumber
         WHERE E.EmployeeNumber IS NULL
         ORDER BY T.EmployeeNumber)
     SELECT DISTINCT
            (T_EmployeeNumber) AS EmployeeNum
     FROM tbl_name;

Also, Looking at the query, the below will also work better than your current query

SELECT
        *
        FROM tblEmployee E
            WHERE EXISTS
            (
                SELECT 1 FROM tblTransaction T WHERE EmployeeNumber = E.EmployeeNumber
            )

this will give the same result with a better performace

Upvotes: 5

Related Questions