select top 1 count(*) returning count of more than one record?

Ok I'm building a query to create a view in SQL server 2005 for work. One of the columns of the view is a simple 0/1 answer.

0 = the user in this record, has no records in TableB
1 = the user in this record has 1 or more records in TableB

So that column is simply a subquery (below)

The layout of TableB is as follows:
SHCDISTN
SHCRCD
SHCSTDT
SHCNBR
SCHCODE

there are no primary keys or relations to this table really. The SHCSTDT is a student number, so every medical 'code' has it's own record in the table with the corresponding student number. So student number "xyz" may have 3 records, no records, or 10000 record in TableB. Now given that, back to the subquery.

I figured I'd be tricky and simply do a count() for all records with that student number (xyz) but limit the result set to 1 (so the response is either 0 or 1 as desired).


The only issue is when the student number (xyz in this case) has 2 or more records in the table, instead of returning a "1", it returns the number of records they have, ignoring the "TOP 1" result set limit.

I come from a MySQL background so the MSSQL is still a little new to me. Am I missing a certain keyword or something? I've also tried to "group by SHCSTDT" but still get the same result. If needed I can clarify better.
////////////sub query
      (
        SELECT TOP 1
            COUNT(SHCSTDT) AS total
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
      ) AS [Medical Problems]

Open in new window

LVL 15
MMDeveloperAsked:
Who is Participating?
 
OtanaConnect With a Mentor Commented:
The top 1 will return the first record of the resultset in which the count has already been done, so you get the full count.

Try this for subquery:

(SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.SHC WHERE SHCSTDT = dbo.SBR.SBRSTDT ) THEN 1 ELSE 0 END) AS [Medical Problems]
0
 
TimCotteeHead of Software ServicesCommented:
Hello MMDeveloper,

You need a group by:

        (
          SELECT TOP 1
              COUNT(SHCSTDT) AS total
          FROM
              dbo.SHC
          WHERE
              ( SHCSTDT = dbo.SBR.SBRSTDT )
          GROUP BY SHCSTDT
        ) AS [Medical Problems]

Regards,

TimCottee
0
 
MMDeveloperAuthor Commented:
     (
        SELECT TOP 1
            COUNT(1) AS Expr1
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
        GROUP BY
            SHCSTDT
      ) AS [Medical Problem]


I still get results higher than 1. I now get null, 0, 1, 2, 3, 4, 5, etc.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
MMDeveloperAuthor Commented:
Well I didn't 'fix' it but I just used a workaround for now.


      (
        SELECT
            CASE WHEN COUNT(*) >= 1 THEN '1'
                 ELSE '0'
            END AS Expr1
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
      ) AS [Medical Problem]
0
 
OtanaCommented:
Did you try my solution? It should be faster than yours.
0
 
MMDeveloperAuthor Commented:
Sorry we must have been posting at the same time and I didn't see yours. Your solution worked just fine and I appreciate your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.