count/select query in MS Sql Server 2005

Question

I have a legacy table that contains 'master' records and associated detail records. Detail records are identified by an addition to the master key of "-nnn". I need to find master records that have no detail records, tried several variations, and then finally broke it down to simplest elements.

This works:

select (select count(*) from dbo.client as cl2 
    where cl2.acct_no like (cl1.acct_no + '-%')) as countx, acct_no 
from dbo.client as cl1

and displays the expected zero or non-zero results, depending on how many detail records there are.

However, when I try to use the count results to select only the records with zero detail records, like so:

select (select count(*) from dbo.client as cl2 
    where cl2.acct_no like (cl1.acct_no + '-%')) as countx, acct_no 
from dbo.client as cl1 
where countx = 0

I get an error: "Invalid column name 'countx'"

What am I missing here?

you can't use column alias in where clause. That's the error. — Rahul
– Rahul, Commented May 22, 2012 at 14:11

Eoin Campbell · Accepted Answer · 2012-05-22 14:15:28Z

1

CountX is not a named column.

This may not be the most optimal way, but wrapping the entire query might work.

SELECT CountX, Acct_No
FROM
(
    select (select count(*) from dbo.client as cl2 
        where cl2.acct_no like (cl1.acct_no + '-%')) as countx, acct_no 
    from dbo.client as cli
) As InnerQuery
where countx = 0

Edit

Looking at this, it may be better Accomplished using a Join, instead of that nested Select.

SELECT     cl1.acct_no, Count(*)
FROM       dbo.client cl1 --the master key numbers
INNER JOIN dbo.client cl2 --the sub-keys
           ON cl1.acct_no NOT LIKE '%-%' --No dashes in the MasterKey
           AND cl1.acct_no LIKE (cl2.acct_no + '-%')
GROUP BY   cl1.acct_no

edited May 22, 2012 at 14:15

answered May 22, 2012 at 14:09

Eoin Campbell

44.4k18 gold badges107 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JESii Over a year ago

Hmmm... So I can use column names but not column aliases in a where clause? Your query worked once I changed 'cli' to 'cl1' in the second 'from'... thanks; that gets me started on my real query.

Amir Ismail · Accepted Answer · 2012-05-22 14:15:10Z

0

Try using in to get what you want

 select Acct_No from dbo.client where Acct_No not in 
  (
    select acct_no from dbo.client where acct_no like acct_no + '-%'
  )

answered May 22, 2012 at 14:15

Amir Ismail

3,8713 gold badges22 silver badges35 bronze badges

Collectives™ on Stack Overflow

count/select query in MS Sql Server 2005

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related