Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2014
    Posts
    4

    Unanswered: Comparing 2similar columns in different tables

    Hi All,

    I am trying to write an SQL command for my crystal report.
    I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.
    Eg: Considering one customer account
    Table 1
    Cust.No Name Amt_Counter AmtPaid
    123.456 sam 0 0
    123.456 sam 1 50

    Table 2
    Cust.No Name Freq_Counter Frequency
    123.456 sam 1 0
    123.456 sam 2 15

    Table 3
    Cust.No Name Date_Counter Start Date
    123.456 sam 0 NULL
    123.456 sam 3 09/05/2009

    Required result -
    Cust.No Name Counter AmtPaid Freq Start Date
    123.456 Sam 0 0 Null Null
    123.456 Sam 1 50 0 Null
    123.456 Sam 2 Null 15 Null
    123.456 Sam 3 Null Null 09/05/2009

    Appreicate any help !!!
    Sorry for the long question..
    Thnx

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No temporary table needed.
    The key here are JOINs.

    Here's how to join two of your tables together. Have a look and try and extend this to include your third [and fourth] tables.

    If you have difficulty post back what you've tried and we'll give you pointers.
    Code:
    SELECT table1.custno
         , table1.name
         , table1.Amt_Counter
         , table1.AmtPaid
         , table2.Freq_Counter
         , table2.Frequency
    FROM   table1
     INNER -- or LEFT (inner=matches in both tables. left=all items from the first table and match where possible from the second)
      JOIN table2
        ON table2.custno = table1.custno
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try FULL OUTER JOIN, like...

    Code:
    SELECT COALESCE(t1.Cust_No , t2.Cust_No , t3.Cust_No) AS Cust_No
         , COALESCE(t1.name    , t2.name    , t3.name   ) AS name
         , COALESCE(t1.Amt_Counter , t2.Freq_Counter , t3.Date_Counter) AS Counter
         , t1.AmtPaid
         , t2.Frequency
         , t3.Start_Date
     FROM  Table_1 AS t1
     FULL  OUTER JOIN
           Table_2 AS t2
      ON   t2.Cust_No      = t1.Cust_No
      AND  t2.Freq_Counter = t1.Amt_Counter
     FULL  OUTER JOIN
           Table_3 AS t3
      ON   t3.Cust_No      = COALESCE(t2.Cust_No      , t1.Cust_No    )
      AND  t3.Date_Counter = COALESCE(t2.Freq_Counter , t1.Amt_Counter)
     ORDER BY
           Cust_No
         , Counter
    ;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that both of this issue and the issue in this thread http://www.dbforums.com/microsoft-sq...sub-query.html
    might be in a category of problems solved by "FULL OUTER JOIN ... FULL OUTER JOIN ..." idiom.

Posting Permissions

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