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:
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.
SELECT TOP 1
COUNT(SHCSTDT) AS total
( SHCSTDT = dbo.SBR.SBRSTDT )
) AS [Medical Problems]