Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Johannesburg, South Africa
    Posts
    28

    Unanswered: Comparing result set values of 2 queries ??

    Any assistance would be so helpful !!

    We have 2 tables.. lets call them INV and COST

    Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

    ID | AMOUNT | VAT ( INV TABLE )
    1 |20.125 |2.896
    2 |10.524 |1.425

    ID | AMOUNT | VAT ( COST TABLE )
    1 |20.125 |4.821 .... different to ID 1 in INV Table
    2 |10.524 |1.425

    If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

    Thats it ???

    Please could someone out there offer some ideas ?

    THANKS

    JON

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    select t1.[id] from [inv table] t1 inner join [cost table] t2 on t1.[id]=t2.[id] where t1.amount+t1.vat<>t2.amount+t2.vat

  3. #3
    Join Date
    Jun 2003
    Location
    Johannesburg, South Africa
    Posts
    28

    Multiple Rows however...

    Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED

    I have a slight problem though...

    Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.

    Table INV has single row entries for each ID(Primary Key)

    Basically, INV table has 1 row, say ID 7
    COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.

    ie.

    COST
    id7 12 4
    id7 21 7
    id7 35 1
    id7 10 87 ...TOTAL 177

    INV
    id7 78 99 ...TOTAL 177 ..In this case all is well

    Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...

    Am I making much sense...

    CHEERS

  4. #4
    Join Date
    Jun 2003
    Location
    Johannesburg, South Africa
    Posts
    28

    Multiple Rows however...

    Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED

    I have a slight problem though...

    Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.

    Table INV has single row entries for each ID(Primary Key)

    Basically, INV table has 1 row, say ID 7
    COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.

    ie.

    COST
    id7 12 4
    id7 21 7
    id7 35 1
    id7 10 87 ...TOTAL 177

    INV
    id7 78 99 ...TOTAL 177 ..In this case all is well

    Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...

    Am I making much sense...

    CHEERS

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select t1.[id] from [inv table] t1 inner join (select t2.[id], cost_total=sum(t2.amount+t2.vat) from [cost table] t2 where t1.[id]=t2.[id] group by t2.[id]) co where t1.[id] = co.[id] and (t1.amount+t1.vat)<>co.cost_total
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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