CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
rbartlejr
rbartlejr

Reputation: 13

Doing self join to hide duplicates - getting error message

Ok, I'm doing a self query to suppress duplicates for a view. The code I'm using is:

    USE BILLING

    SELECT Provider_Code,
           Provider_LName,
           Provider_Fname,
           Provider_Title,
           AGENCY_LOCATION_NAME_LINE_1,
           CostCenter_AbbrName
    FROM   dbo.ServiceProfiler prov1
           INNER JOIN
           dbo.ServiceProfiler prov2
           ON prov1.Provider_Code = prov2.Provider_Code
    WHERE  0 = (SELECT COUNT(s1.Provider_Code)
                FROM   prov1
                WHERE  prov2.Provider_Code = prov1.Provider_Code
                       AND prov2.Provider_Code < prov1.Provider_Code);

When executing the query on SQL Server 2005 I get the message:

Msg 208, Level 16, State 1, Line 3
Invalid object name 'prov1'.

I cannot for the life of me understand why the alias names are invalid. I think I found a similar question here. But it's very generic and doesn't show a full 'real' query using the aliases.

Upvotes: 1

Views: 235

Answers (4)

Norman
Norman

Reputation: 453

I'd do something more like this:

SELECT Provider_Code,
           Provider_LName,
           Provider_Fname,
           Provider_Title,
           AGENCY_LOCATION_NAME_LINE_1,
           CostCenter_AbbrName
FROM   dbo.ServiceProfiler prov,
     (SELECT Provider_Code,count(*) Count from dbo.ServiceProfiler group by Provider_Code HAVING count(*)=0) q
WHERE prov.Provider_Code = q.Provider_Code

You could also do a straight up sub-select, I'd imagine. I think joining on a subquery is easier to understand, but that's me.

Upvotes: 2

Malachi
Malachi

Reputation: 3221

you can't call the prov1 table inside the select of the where statement, you have to call ServiceProfiler AS Prov3 and change the Where Statement as @RB Said

Note also that your where clause in your subquery does not make sense. prov2.ProviderCode cannot be simultaneously less-than AND equal-to prov1.ProviderCode.

for it to work

Upvotes: 0

Gonzalo.-
Gonzalo.-

Reputation: 12682

FROM   prov1

here is your problem; since is another select, you need to specify the table where you select from. You can use the alias prov1 in the where's of that select, to join, but need to specify the table, and also, a new alias.

Upvotes: 0

RB.
RB.

Reputation: 37212

You cannot use FROM prov1 - there is no table called prov1. As per the documentation, FROM is used with a *table_source*, which is a table or view name.

You can reference prov1 from the where clause of your subquery however.

Note also that your where clause in your subquery does not make sense. prov2.ProviderCode cannot be simultaneously less-than AND equal-to prov1.ProviderCode.

Upvotes: 0

Related Questions