SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HELP ME::Comparing Numbers from two different tables.

    Hello Everyone,
    How can I be comparing numbers from different two tables.
    I have two two tables in in a database sitting on Mysql database.

    The first table has the following attributes
    id
    claimcode
    Amount

    Then second table has the following attributes
    id
    claimcode
    Amount
    Now I want to be comparing values of amount from two different table that have same id and Claim code.

    For example if the there are records as follows
    Records in the first table ;
    2 Sub 5000
    2 Fairs 50000
    2 Departure_Fees 3500
    2 Total 58500
    Records in the second table ;
    2 Sub 6000
    2 Fairs 50000
    2 Departure_Fees 3500
    2 Total 59500

    Now I would like to be comparing these figures using php and mysql, then write the differences for each corrosponding entry.e.g
    Sub 1000
    Fairs 0
    Departure_Fees 0
    Total 1000
    Please help me with code or explanation.

  2. #2
    SitePoint Zealot trekmp's Avatar
    Join Date
    Dec 2005
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should be able to do this in one sql statement

    Code:
    SELECT t1.id,t1.claim,t1.amount,t2.amount,t2.amount-t1.amount AS MyDiff FROM table1 t1
    LEFT OUTER JOIN table2 t2 ON t1.id=t2.id AND t1.claim=t2.claim
    Then you can use PHP to give you the relevant totals you need
    Why do we exist? What is our purpose?
    >H2O Developments
    >USS Endeavour
    >Hosted@Servage

  3. #3
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Iam using the code below but I receiving the following error saying " 1066 is not unique table/alias beforetravel2". Where Iam going wrong????



    SELECT aftertravel2.AllowanceId,beforetravel2.AllowanceId, aftertravel2.ClaimCode, aftertravel2.Amount, beforetravel2.ClaimCode,beforetravel2.Amount,aftertravel2.Amount+beforetravel2.Amount as MyDiff
    FROM aftertravel2, beforetravel2
    LEFT OUTER JOIN beforetravel2 ON aftertravel2 ON aftertravel2.AllowanceId = beforetravel2.AllowanceId AND aftertravel2.ClaimCode= beforetravel2.ClaimCode

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT aftertravel2.AllowanceId
         , beforetravel2.AllowanceId
         , aftertravel2.ClaimCode
         , aftertravel2.Amount
         , beforetravel2.ClaimCode
         , beforetravel2.Amount
         , aftertravel2.Amount + beforetravel2.Amount as MyDiff
      FROM aftertravel2
    LEFT OUTER 
      JOIN beforetravel2 
        ON beforetravel2.AllowanceId = aftertravel2.AllowanceId
       AND beforetravel2.ClaimCode   = aftertravel2.ClaimCode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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