# Database Discussions > Microsoft SQL Server 2008 >  Joining tables

## ironcurtain

Hi,

I need to make a query some joins but I met some problem.
I have 2 tables connected many to one relationship.

Tables are Systems (SystemID, SystemName) and Servers (ServerID, ServerName, SystemID, RegionID) (one system can have many servers)
I need to use Servers table to make join with Region table bacause I want to count Systems in each region.

I tried to do this with joins but it doesn't work because I always get servers count.

Any ideas how it can be done?

----------


## rmiao

How does region table look like?

----------


## ironcurtain

The region table has 2 columns: regionID, regionName. This is dictionary table with 6 rows.

----------


## skhanal

Use group by RegionID and COUNT to get servers for each region.

----------


## ironcurtain

Thank You skhanal. I just figured it out by myself a few moments ago.

----------


## ironcurtain

I made some changes and now I am trying to use CTE and Over clause to count records.

I created below query byt it doesn't work. I got error:



> Incorrect syntax near 'distinct'.





> -- Define the CTE expression name and column list.
> WITH Counter_CTE (SystemID, SystemName, ServerID, ServerName, SystemModel, SystemType, SystemGroup, RemsCode, City, Region)
> AS
> -- Define the CTE query.
> (
> select
> sy.SystemID, sy.SystemName, se.ServerID, se.servername, smd.systemmodel, st.systemtype, sg.systemgroup, re.remscode, ci.city, co.region
> from 
> Systems sy 
> ...


I found that distnct can be used with count.
Any ideas how it should look like?

----------

