By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Multi-table JOIN issue

P: 6
Hi all. Here's what I'm encountering in SQL 2000.

I've got the following tables:

program
------------
id (pk int)

program_component_type_rel
------------
program_ID (fk int), program_component_type_ID (fk int), is_group (bit), is_individual (bit)

program_component_type
------------
id (pk int)

My webform is a search form, trying to find all programs, that have is_group and is_individual values set to true. There are several hundred programs, with varying combinations of what it links to in the program_component_type_rel table.

Now, what I'm doing to try and find all that match is create a dynamic SQL statement (programmatically using ColdFusion) that creates the table aliases dynamically so I can match everything up. It ends up looking like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT        DISTINCT p.name, p.department, 
  2.             p.address1, p.address2, p.city, p.prov_state, p.postal_zip, p.country, 
  3.             p.phone, p.fax, p.website, 
  4.             p.medical_director_name, p.program_director_name, 
  5.             p.caseload_per_year, p.cost_to_participate_cost, p.cost_to_participate_frequency, p.is_subsidy_avail, 
  6.             p.exercise_onsite_per_week, p.exercise_offsite_per_week, 
  7.             p.is_active, p.is_automatic_referral, p.date_created, 
  8.             mpt_MD.name AS MD_profession, mpt_PD.name AS PD_profession,
  9.             pdt.name AS program_duration,
  10.             pt.name AS program_type,
  11.             plt.name AS program_location,
  12.             pvt.name AS prevention_type,
  13.             tpst.name AS target_population_specialty
  14. FROM        program AS p
  15. LEFT JOIN    member_profession_type AS mpt_MD
  16. ON            p.medical_director_profession_type_ID = mpt_MD.id
  17. LEFT JOIN    member_profession_type AS mpt_PD
  18. ON            p.program_director_profession_type_ID = mpt_PD.id
  19. LEFT JOIN    program_duration_type AS pdt
  20. ON            p.program_duration_type_ID = pdt.id
  21. LEFT JOIN    program_type AS pt
  22. ON            p.program_type_ID = pt.id
  23. LEFT JOIN    program_location_type AS plt
  24. ON            p.program_location_type_ID = plt.id
  25. LEFT JOIN    prevention_type AS pvt
  26. ON            p.prevention_type_ID = pvt.id
  27. LEFT JOIN    target_population_specialty_type AS tpst
  28. ON            p.target_population_specialty_type_ID = tpst.id
  29. INNER JOIN    program_component_type_rel AS pctrG1
  30. ON            p.id = pctrG1.program_ID
  31. INNER JOIN    program_component_type_rel AS pctrI1
  32. ON            p.id = pctrI1.program_ID
  33. INNER JOIN    program_component_type_rel AS pctrG5
  34. ON            p.id = pctrG5.program_ID    
  35. INNER JOIN    program_component_type_rel AS pctrI5
  36. ON            p.id = pctrI5.program_ID
  37. INNER JOIN    program_component_type_rel AS pctrG4
  38. ON            p.id = pctrG4.program_ID
  39. INNER JOIN    program_component_type_rel AS pctrI4
  40. ON            p.id = pctrI4.program_ID
  41. WHERE        0=0
  42. AND        pctrG1.is_group = 1
  43. AND        pctrI1.is_individual = 1
  44. AND        pctrG5.is_group = 1
  45. AND        pctrI5.is_individual = 1
  46. AND        pctrG4.is_group = 1
  47. AND        pctrI4.is_individual = 1
  48. ORDER BY     p.name, p.city, p.prov_state, p.country
  49.  
This is taking forever to load, so I'm thinking there MUST be a better way to do this. No? Any help is appreciated. Please let me know if you need more info. Thanks for your time.
Aug 10 '09 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 145
I think you're doing way too much right here for what you are trying to accomplish.

First. Why are you INNER JOINing the same program_component_type_rel table multiple times? I would suggest removing :
Expand|Select|Wrap|Line Numbers
  1.  INNER JOIN    program_component_type_rel AS pctrI1
  2.  ON            p.id = pctrI1.program_ID
  3.  INNER JOIN    program_component_type_rel AS pctrG5
  4.  ON            p.id = pctrG5.program_ID    
  5.  INNER JOIN    program_component_type_rel AS pctrI5
  6.  ON            p.id = pctrI5.program_ID
  7.  INNER JOIN    program_component_type_rel AS pctrG4
  8.  ON            p.id = pctrG4.program_ID
  9.  INNER JOIN    program_component_type_rel AS pctrI4
  10.  ON            p.id = pctrI4.program_ID
Second: What are you doing with this constant code here:
Expand|Select|Wrap|Line Numbers
  1.  WHERE        0=0
Third: Why use DISTINCT ? Your program table has unique rows by virtue of PK_id and all JOINS are LEFT on your program table.


Here's how I would do what I think your trying to do:
Expand|Select|Wrap|Line Numbers
  1. SELECT      p.name, p.department, 
  2.             p.address1, p.address2, p.city, p.prov_state, p.postal_zip, p.country, 
  3.             p.phone, p.fax, p.website, 
  4.             p.medical_director_name, p.program_director_name, 
  5.             p.caseload_per_year, p.cost_to_participate_cost, p.cost_to_participate_frequency, p.is_subsidy_avail, 
  6.             p.exercise_onsite_per_week, p.exercise_offsite_per_week, 
  7.             p.is_active, p.is_automatic_referral, p.date_created, 
  8.             mpt_MD.name AS MD_profession, mpt_PD.name AS PD_profession,
  9.             pdt.name AS program_duration,
  10.             pt.name AS program_type,
  11.             plt.name AS program_location,
  12.             pvt.name AS prevention_type,
  13.             tpst.name AS target_population_specialty
  14. FROM        program AS p
  15. LEFT JOIN    member_profession_type AS mpt_MD
  16. ON            p.medical_director_profession_type_ID = mpt_MD.id
  17. LEFT JOIN    member_profession_type AS mpt_PD
  18. ON            p.program_director_profession_type_ID = mpt_PD.id
  19. LEFT JOIN    program_duration_type AS pdt
  20. ON            p.program_duration_type_ID = pdt.id
  21. LEFT JOIN    program_type AS pt
  22. ON            p.program_type_ID = pt.id
  23. LEFT JOIN    program_location_type AS plt
  24. ON            p.program_location_type_ID = plt.id
  25. LEFT JOIN    prevention_type AS pvt
  26. ON            p.prevention_type_ID = pvt.id
  27. LEFT JOIN    target_population_specialty_type AS tpst
  28. ON            p.target_population_specialty_type_ID = tpst.id
  29. INNER JOIN    program_component_type_rel AS pctr
  30. ON            p.id = pctr.program_ID
  31. WHERE
  32. AND        pctr.is_group = 1
  33. AND        pctr.is_individual = 1
  34. ORDER BY     p.name, p.city, p.prov_state, p.country
  35.  
Aug 12 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.