Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    2

    Post Unanswered: Select from 2 tables cause a loop... What am I doing wrong?

    I have two tables that I wish to return values for and populate a text file with the returned values. This part works well, however, I get strange values when I rung the Select statement against both tables.
    For example, if I run this statement:
    Select count(page_count), sum(Page_count) from Contracts
    this will return the values I expect
    If I were to add another table to this query then things go crazy. For instance, if I took the same query and added the Maps table to it as such:
    Select count(page_count), sum(Page_count) from Contracts, Maps
    I get crazy numbers that are many times more then the actual numbers

    My goal is to get the page count, number of records for Contracts and number of records from Maps all in one query

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    you need to join the tables on a column.

    If you don't have join statement you are getting a cross join
    thus for every entry in the maps table say(10)
    you will get 10 times the original number.

  3. #3
    Join Date
    Apr 2006
    Posts
    2

    How do I fix that?

    if the tables are not liked or related, is there a way to get the data from two tables using one query?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sure. With enough hackneyed SQL code its possible to do lots of things you should never do.
    Time to go back and review the concept of "Relational Database"....Start with Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hyabusact
    My goal is to get the page count, number of records for Contracts and number of records from Maps all in one query
    it's easy, and there are several ways to do it
    Code:
    select count(page_count)  as daCount
         , sum(Page_count)    as daSum
         , 'Contracts'        as daTable
      from Contracts
    union all
    Select count(page_count)
         , sum(Page_count)
         , 'Maps'
      from Maps
    Code:
    select ( select count(page_count) from Contracts ) as Contract_count
         , ( select sum(Page_count)   from Contracts ) as Contract_sum
         , ( select count(page_count) from Maps      ) as Map_count
         , ( select sum(Page_count)   from Maps      ) as Map_sum
    Code:
    select donner  as CountContracts
         , blitzen as SumContracts
         , pushme  as CountMaps
         , pullyou as SumMaps
      from (
           select count(page_count)  as donner
                , sum(Page_count)    as blitzen
             from Contracts
           ) as frick
    cross
      join (
           select count(page_count)  as pushme
                , sum(Page_count)    as pullyou
             from Maps
           ) as frack
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, I guess if you are just collecting table stats thats alright.

    My preference would be the UNION method that Rudy posted.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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