Results 1 to 12 of 12

Thread: SQL Statement

  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Red face Unanswered: SQL Statement

    Hey Gang, I need a sql statement that will list the distinct name & count from 2 tables. I need to count the region and the number for that region.

    SELECT dbo.tblRegions.plan_id AS Expr1, dbo.tblRegionType.region_type, dbo.tblMonitoringPrograms.New_Plan_ID
    FROM dbo.tblRegionType INNER JOIN
    dbo.tblRegions ON dbo.tblRegionType.region_id = dbo.tblRegions.region_id INNER JOIN
    dbo.tblMonitoringPrograms ON dbo.tblRegions.plan_id = dbo.tblMonitoringPrograms.Old_Plan_ID

    This is what I have so far

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    give us a wee bit of a hint

    how many plans in a region? how many regions in a program? how many programs in a region? how many plans in a region? how many plans in a program?

    in other words, what are the one-to-many relationships between your three tables?

    also, did you want regions and count of programs by plan, or regions and count of plans by program?

    i am *so* confused
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Smile SQL statement

    I'm am too, The way it should look is

    Regions Count

    North Atlantic 5
    Pacific 17
    Mid Atlantic
    Something along those lines.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, well, that's nice, but without the information about how the tables are related, the best i can guess at is the following:
    Code:
    select dbo.tblRegionType.region_type  
         , count(*)
      from dbo.tblRegionType 
    inner 
      join dbo.tblRegions 
        on dbo.tblRegionType.region_id 
         = dbo.tblRegions.region_id 
    inner 
      join dbo.tblMonitoringPrograms 
        on dbo.tblRegions.plan_id 
         = dbo.tblMonitoringPrograms.Old_Plan_ID
    group
        by dbo.tblRegionType.region_type
    which i am almost certain is not going to be what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink SQL statement

    Ok, OK, I'm alittle stir crazy today it's friday and Ive been working on asp.net for 6 day a week since december. Then They but the in the hat and say we need the by monday. Sorry. I dont know what I'm thinking about. Please check the attachment.
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i clicked on that attachment and IE tried to open the doc, and crashed (and took down all open browser windows, i might add)

    then i right-clicked and saved the doc on my desktop, opened it in word, and it froze my machine, i wasn't able to unfreeze it until i somehow managed to bring Task Manager up and kill word

    please describe your problem in something other than a word doc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Unhappy SQL statement

    Sorry, try this one

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, try which one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry SQL statement

    SELECT DISTINCT dbo.tblRegionType.region_type AS Region, COUNT(*) AS [Count]
    FROM dbo.tblMonitoringPrograms INNER JOIN
    dbo.tblRegions ON dbo.tblMonitoringPrograms.New_Plan_ID = dbo.tblRegions.plan_id INNER JOIN
    dbo.tblRegionType ON dbo.tblRegions.region_id = dbo.tblRegionType.region_id
    GROUP BY dbo.tblRegionType.region_type

  10. #10
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Thumbs down SQL statement

    This is what I have so far. I just happen to inherite a codder database. I had to add new field onto everything. I can see the listing I want. Just not on the entire table.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your query looks fine (but remove the DISTINCT -- groups are distinct by definition of GROUP BY)

    what's a codder database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    SQL statement

    Sorry, I ment Coder database. That a database with no referencial intergritty. No data-definitions. etc... I run across it alot with the students right out of School and from the late 90's. They just did'nt teach this stuff back in the day. I mean, A database is still a electronic filling cabanet right. I go to a cabanet and open a file. I dont wont to go to the third peace of paper in the file and look in another cabanet because of a reference.

Posting Permissions

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