Results 1 to 6 of 6

Thread: REsult Set...

  1. #1
    Join Date
    Oct 2003
    Location
    Bangalore, India
    Posts
    27

    Post Unanswered: REsult Set...

    How do i compare two select queries and get a true or false result on the output of the two queries?

    If the two select queries are having same result the display should be true and if the two select queries are having different result then the display should be false.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    run both queries as part of a UNION statement and count how many rows you get after duplicate rows have been removed --

    select count(*)
    from (
    query1
    union
    query2
    ) as combinedresults

    now get counts of the queries separately --

    select count(*)
    from (
    query1
    ) as query1results
    union all
    select count(*)
    from (
    query2
    ) as query2results

    this second query will give you two rows, and if the sum of the two numbers is *not* equal to the number from the combined result, then the two queries have the same result
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Interesting question!

    If you want to check both the data and the rowcount, try this:

    select ~ cast(count(*) as bit)
    from
    (select Checksum(*) Query1Checksum
    from
    ([Your first query statement goes here]) SubQuery1) Query1
    Full outer join
    (select Checksum(*) Query2Checksum
    from
    ([Your second query statement goes here]) SubQuery2) Query2
    on Query1.Query1Checksum = Query2.Query2Checksum
    where Query1.Query1Checksum is null or Query2.Query2Checksum is null

    This will return a bit value of 0 if the two outputs are NOT the same, and 1 if they are.

    Cheers.

    blindman

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmmm, i looked all through BOL, can't seem to locate your Checksum function

    that tilde looks plenty suspicious too


  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The tilde is a bitwise NOT operator that just changes the bit value from 1 to 0 or 0 to 1, so that True = 1 and False = 0. Purely cosmetic.

    CHECKSUM and BINARY_CHECKSUM are in Books Online. Search the index again. Perhaps you made a typo.

    As luck would have it, not fifteen minutes after I posted that code I had occasion to use it in researching a problem at my office!

    blindman

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, no typo

    just not the latest version of sql server

    we're still on sql 7 here

    (something about replication not working correctly when the last attempt was made to upgrade -- i am so not a DBA)

    that tilde trick was rather nice too -- first time i've seen that

    thanks for the food for thought


    rudy

Posting Permissions

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