Embed a count statement in a a select list

Question

I am using Esri ArcMap 10.0 with Microsoft Sql Server 2008 R2

I have 2 tables:

1. MN_SCHOOLS_PUBLIC - the school ID field is called ORGID_REL
2. TRPD_Schdgrps_Schools - the school ID field is called School_Code

The first table is just a list of schools with their addresses, the second table is a list of group reservations made by schools. Every school in table 2 exists in table 1. I am trying to create a query where I generate a list of schools that have made multiple reservations. This is what I came up with:

select School_Code
from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
group by School_Code
having COUNT(*) > 1

This did the trick or at least close enough. However, my problem lies int hat I have to run this query in GIS software, the query tool (Select by Attributes in Esri's ArcMap) ALWAYS starts the select statement for the user. So when you open the tool, the query starts with:

select * from gisadmin.MN_SCHOOLS_PUBLIC

I want to select all the schools in table 1 that have multiple entries in table 2 and I am required to start with the above select statement. This is what I came up with but I can't seem to figure out how to get it to work:

select * from gisadmin.MN_SCHOOLS_PUBLIC
where EXISTS 
(SELECT * 
FROM central2.GISADMIN.TRPD_Schdgrps_Schools 
WHERE central2.GISADMIN.MN_Schools_PUBLIC.ORGID_REL =
(select * from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
where School_Code in
(select School_Code
from central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS
group by School_Code
having COUNT(*) > 1)))

Any help would be greatly appreciated

Answer 1

Try this:

SELECT * FROM gisadmin.MN_SCHOOLS_PUBLIC msp
WHERE msp.School_Code IN
(
    SELECT tss.School_Code
    FROM central2.gisadmin.TRPD_SCHDGRPS_SCHOOLS tss
    GROUP BY tss.School_Code
    HAVING COUNT(*) > 1
)