Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    4

    Unanswered: Problem in Joining Tables..

    Hi,
    I am trying to join 4 tables and i am getting duplicate results when I add the one table. Please help... Following are the details.
    I have 4 tables:
    rep_application_details has columns application_id, client_id
    rep_applicant_details has columns application_id, applicant_type
    rep_deposit_accounts has columns application_id, product_id
    rep_client_products has columns product_id, client_id
    i am trying to execute the following query.

    select distinct d.applicant_type, count(a.product_id) from rep_deposit_accounts a, rep_application_Details b,
    rep_client_products c,rep_applicant_details d
    where
    a.product_id = c.product_id
    and a.application_id = b.application_id
    and b.application_id = d.application_id
    and b.client_id = c.client_id
    and c.product_id = a.product_id
    and b.client_id='1025'
    group by d.applicant_type


    This is returning 71 rows.. where as, when I execute the other query without rep_applicant_details, it is returning only 21 rows. I have multiple rows in rep_deposit_accounts table with same application id with different product_ids. I want to know how to get the count as 1 for each application id even though it has multiple product_ids assigned. Look at the following table results which I am getting now. Where as in my case, I need to get the count as 1 for application id. for ex: count of 1 for 11777 and 1 for 11780 etc.
    Primary 11777 11777 11777 CE_6M 1
    Primary 11777 11777 11777 CH_AC 1
    Primary 11777 11777 11777 DP_S 1
    Primary 11777 11777 11777 O_CO 1
    Primary 11777 11777 11777 O_ES 1
    Primary 11780 11780 11780 CE_24M 1
    Primary 11780 11780 11780 CH_ADC 1
    Primary 11780 11780 11780 DP_HC 1
    Primary 11780 11780 11780 DP_S 1
    Primary 11780 11780 11780 DP_VC 1
    Primary 11780 11780 11780 MM_SMM 1
    Primary 11780 11780 11780 O_CO 1
    Primary 11780 11780 11780 O_ES 1
    Primary 11800 11800 11800 CH_ADC 1
    Primary 11800 11800 11800 DP_S 1
    Primary 11800 11800 11800 O_CO 1
    Primary 11800 11800 11800 O_ES 1
    Primary 11802 11802 11802 CH_ADC 1
    Primary 11802 11802 11802 DP_S 1
    Primary 11802 11802 11802 O_CO 1
    Primary 11802 11802 11802 O_ES 1
    Primary 11837 11837 11837 CH_AC 1
    Primary 11837 11837 11837 DP_HC 1
    Primary 11837 11837 11837 DP_IST 1
    Primary 11837 11837 11837 DP_S 1
    Primary 11837 11837 11837 O_CO 1
    Primary 11837 11837 11837 O_ES 1
    Primary 11842 11842 11842 DP_VC 1
    Primary 11842 11842 11842 O_ES 1
    Primary 11853 11853 11853 CE_24M 1
    Primary 11853 11853 11853 CH_ADC 1
    Primary 11853 11853 11853 DP_HC 1
    Primary 11853 11853 11853 DP_ISR 1
    Primary 11853 11853 11853 DP_S 1
    Primary 11853 11853 11853 DP_VC 1
    Primary 11853 11853 11853 MM_SMM 1
    Primary 11853 11853 11853 O_CO 1
    Primary 11853 11853 11853 O_ES 1
    Primary 11990 11990 11990 DP_VC 1
    Primary 11990 11990 11990 O_ES 1
    Primary 12061 12061 12061 O_CO 1
    Primary 12061 12061 12061 O_ES 1
    Primary 12096 12096 12096 O_ES 1
    Primary 12097 12097 12097 DP_VC 1
    Primary 12097 12097 12097 O_ES 1
    Primary 12115 12115 12115 CH_SC 1
    Primary 12115 12115 12115 O_CO 1
    Primary 12115 12115 12115 O_ES 1
    Primary 12116 12116 12116 CH_AC 1
    Primary 12116 12116 12116 DP_S 1
    Primary 12116 12116 12116 O_CO 1
    Primary 12116 12116 12116 O_ES 1
    Primary 12121 12121 12121 CH_SC 1
    Primary 12121 12121 12121 O_CO 1
    Primary 12121 12121 12121 O_ES 1
    Primary 12122 12122 12122 DP_S 1
    Primary 12122 12122 12122 O_CO 1
    Primary 12122 12122 12122 O_ES 1
    Primary 12123 12123 12123 CH_AC 1
    Primary 12123 12123 12123 O_CO 1
    Primary 12123 12123 12123 O_ES 1
    Primary 12132 12132 12132 CH_SC 1
    Primary 12132 12132 12132 O_CO 1
    Primary 12132 12132 12132 O_ES 1
    Primary 12153 12153 12153 DP_S 1
    Primary 12153 12153 12153 O_ES 1
    Primary 12154 12154 12154 CH_AC 1
    Primary 12154 12154 12154 O_CO 1
    Primary 12154 12154 12154 O_ES 1
    Primary 12155 12155 12155 DP_S 1
    Primary 12155 12155 12155 O_ES 1

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've reformatted your SQL using the SQL-92 syntax to make it easier to focus on the relationships. The new SQL is:
    Code:
    SELECT DISTINCT d.applicant_type, count(a.product_id)
       FROM rep_application_Details AS b
       INNER JOIN rep_deposit_accounts AS a
          ON (a.application_id = b.application_id)
       INNER JOIN rep_client_products AS c
          ON (c.product_id = a.product_id
          AND c.client_number = b.client_number)
       INNER JOIN rep_applicant_details AS d
          ON (d.application_id = b.application_id)
       WHERE  b.client_id='1025'
       GROUP BY d.applicant_type
    Based on this, I'd bet that you are missing a client_number in the ON clause for rep_applicant_details.

    -PatP

  3. #3
    Join Date
    May 2008
    Posts
    4

    Problem in Joining Tables..

    Yes.. I don't have client_id field in my rep_applicant_details table.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe my assumption was incorrect then... Do you have a row in rep_applicant_details where the applicant_type value is "Primary 12155 12155 12155 O_ES" and the count should be 1?

    -PatP

  5. #5
    Join Date
    May 2008
    Posts
    4
    No.. that is the combination of all the four tables.. When I execute the following query I will get those results...

    select d.applicant_type, d.application_id, b.application_id, a.application_id, a.product_id, count(b.application_id)
    from
    rep_deposit_accounts a,
    rep_application_Details b,
    rep_client_products c,
    rep_applicant_details d
    where d.application_id = a.application_id
    and d.application_id = b.application_id
    and a.product_id = c.product_id
    and a.application_id = b.application_id
    and b.client_id = c.client_id
    and c.product_id = a.product_id
    and b.client_id='1025'
    group by d.applicant_type,d.application_id, b.application_id, a.application_id,a.product_id

Posting Permissions

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