Results 1 to 6 of 6

Thread: Joining tables

  1. #1
    Join Date
    Sep 2012
    Posts
    11

    Joining tables

    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?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    How does region table look like?

  3. #3
    Join Date
    Sep 2012
    Posts
    11
    The region table has 2 columns: regionID, regionName. This is dictionary table with 6 rows.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Use group by RegionID and COUNT to get servers for each region.

  5. #5
    Join Date
    Sep 2012
    Posts
    11
    Thank You skhanal. I just figured it out by myself a few moments ago.

  6. #6
    Join Date
    Sep 2012
    Posts
    11
    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
    inner join Servers se on sy.SystemID = se.systemid
    inner join Systemmodels smd on sy.systemmodelid = smd.SystemModelID
    inner join SystemTypes st on smd.SystemTypeID = st.SystemTypeID
    inner join SystemGroups sg on st.SystemGroupID = sg.SystemGroupID
    inner join REMS re on se.REMSID = re.REMSID
    inner join Cities ci on re.CityID = ci.CityID
    inner join Countries co on ci.CountryID = co.CountryID
    --order by sy.SystemName
    )
    -- Define the outer query referencing the CTE name.
    SELECT SystemID, region, COUNT(distinct SystemID) over (partition by systemid) AS TotalSsytems
    FROM Counter_CTE
    ORDER BY TotalSsytems desc
    GO
    I found that distnct can be used with count.
    Any ideas how it should look like?
    Last edited by ironcurtain; 05-28-2013 at 11:22 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •