Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2008

    Unanswered: Inner Join 3 tables problem

    (Updated: at georgev's suggestion, I provide some samples. Hope you understand me)

    I have three tables like these:

    Application_t (~10K rows)
    App_code Cust_ID
    A1 C1
    A2 C2
    A3 C2
    A4 C2
    A5 C3

    Cust_t1 (1K rows)
    Cust_ID DOB Name
    C1 6/6/81 Johnny
    C2 6/6/87 Amy
    C3 1/4/73 Julie

    Cust_t2 (3K rows)
    Cust_ID Company Ttile...
    C1 Johnny'sCo Director
    C2 Amy'sCo Manager
    C3 Julie'sCo CFO

    (Cust_t1 and Cust_t2 contain different customer info of the customers
    Each customer can have numerous applications or no applications at all

    I want to obtain a list of applications with the corresponding customer info from cust_t1 and cust_t2. So the number of rows in the output should have 10K rows, the same as that of application_t.

    Expected Output:
    App_code Cust_ID name Company
    A1 C1 Johnny Johnny'sCo
    A2 C2 Amy Amy'sCo
    A3 C2 Amy Amy'sCo
    A4 C2 Amy Amy'sCo
    A5 C3 Julie Julie'sCo

    So I used the following query:

    SELECT Application_t.App_code, Application_t.cust_ID as Cust_ID, name, Company
    FROM (Application_t INNER JOIN Cust_t1 ON Application_t.cust_ID = Cust_t1.REF_NUM) INNER JOIN Cust_t2 ON Cust_t1.cust_id = Cust_t2.cust_id;

    But the output had more than more than 70K rows. I think my query is wrong. I'd appreciate it if you'd tell me how to get my desired output.
    Last edited by kennyming; 07-17-08 at 04:25.

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 10
    Quote Originally Posted by kennyming
    Application_t (~10K rows)
    Quote Originally Posted by kennyming
    the output should have 30K rows, the same as that of application_t.

    Now, can you provide us with some sample data from each of your tables and show us what your expected output should be please?
    Home | Blog

Posting Permissions

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