Multi-table JOIN issue

[nmailey]

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

I've got the following tables:

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

program_compone nt_type_rel
------------
program_ID (fk int), program_compone nt_type_ID (fk int), is_group (bit), is_individual (bit)

program_compone nt_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_compone nt_type_rel table.

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

SELECT		DISTINCT p.name, p.department, 
            p.address1, p.address2, p.city, p.prov_state, p.postal_zip, p.country, 
            p.phone, p.fax, p.website, 
            p.medical_director_name, p.program_director_name, 
            p.caseload_per_year, p.cost_to_participate_cost, p.cost_to_participate_frequency, p.is_subsidy_avail, 
            p.exercise_onsite_per_week, p.exercise_offsite_per_week, 
            p.is_active, p.is_automatic_referral, p.date_created, 
            mpt_MD.name AS MD_profession, mpt_PD.name AS PD_profession,
            pdt.name AS program_duration,
            pt.name AS program_type,
            plt.name AS program_location,
            pvt.name AS prevention_type,
            tpst.name AS target_population_specialty
FROM		program AS p
LEFT JOIN	member_profession_type AS mpt_MD
ON			p.medical_director_profession_type_ID = mpt_MD.id
LEFT JOIN	member_profession_type AS mpt_PD
ON			p.program_director_profession_type_ID = mpt_PD.id
LEFT JOIN	program_duration_type AS pdt
ON			p.program_duration_type_ID = pdt.id
LEFT JOIN	program_type AS pt
ON			p.program_type_ID = pt.id
LEFT JOIN	program_location_type AS plt
ON			p.program_location_type_ID = plt.id
LEFT JOIN	prevention_type AS pvt
ON			p.prevention_type_ID = pvt.id
LEFT JOIN	target_population_specialty_type AS tpst
ON			p.target_population_specialty_type_ID = tpst.id
INNER JOIN	program_component_type_rel AS pctrG1
ON			p.id = pctrG1.program_ID
INNER JOIN	program_component_type_rel AS pctrI1
ON			p.id = pctrI1.program_ID
INNER JOIN	program_component_type_rel AS pctrG5
ON			p.id = pctrG5.program_ID	
INNER JOIN	program_component_type_rel AS pctrI5
ON			p.id = pctrI5.program_ID
INNER JOIN	program_component_type_rel AS pctrG4
ON			p.id = pctrG4.program_ID
INNER JOIN	program_component_type_rel AS pctrI4
ON			p.id = pctrI4.program_ID
WHERE		0=0
AND		pctrG1.is_group = 1
AND		pctrI1.is_individual = 1
AND		pctrG5.is_group = 1
AND		pctrI5.is_individual = 1
AND		pctrG4.is_group = 1
AND		pctrI4.is_individual = 1
ORDER BY 	p.name, p.city, p.prov_state, p.country

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.

[Jerry Winston]

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_compone nt_type_rel table multiple times? I would suggest removing :

 INNER JOIN    program_component_type_rel AS pctrI1
 ON            p.id = pctrI1.program_ID
 INNER JOIN    program_component_type_rel AS pctrG5
 ON            p.id = pctrG5.program_ID    
 INNER JOIN    program_component_type_rel AS pctrI5
 ON            p.id = pctrI5.program_ID
 INNER JOIN    program_component_type_rel AS pctrG4
 ON            p.id = pctrG4.program_ID
 INNER JOIN    program_component_type_rel AS pctrI4
 ON            p.id = pctrI4.program_ID

Second: What are you doing with this constant code here:

 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:

SELECT      p.name, p.department, 
            p.address1, p.address2, p.city, p.prov_state, p.postal_zip, p.country, 
            p.phone, p.fax, p.website, 
            p.medical_director_name, p.program_director_name, 
            p.caseload_per_year, p.cost_to_participate_cost, p.cost_to_participate_frequency, p.is_subsidy_avail, 
            p.exercise_onsite_per_week, p.exercise_offsite_per_week, 
            p.is_active, p.is_automatic_referral, p.date_created, 
            mpt_MD.name AS MD_profession, mpt_PD.name AS PD_profession,
            pdt.name AS program_duration,
            pt.name AS program_type,
            plt.name AS program_location,
            pvt.name AS prevention_type,
            tpst.name AS target_population_specialty
FROM        program AS p
LEFT JOIN    member_profession_type AS mpt_MD
ON            p.medical_director_profession_type_ID = mpt_MD.id
LEFT JOIN    member_profession_type AS mpt_PD
ON            p.program_director_profession_type_ID = mpt_PD.id
LEFT JOIN    program_duration_type AS pdt
ON            p.program_duration_type_ID = pdt.id
LEFT JOIN    program_type AS pt
ON            p.program_type_ID = pt.id
LEFT JOIN    program_location_type AS plt
ON            p.program_location_type_ID = plt.id
LEFT JOIN    prevention_type AS pvt
ON            p.prevention_type_ID = pvt.id
LEFT JOIN    target_population_specialty_type AS tpst
ON            p.target_population_specialty_type_ID = tpst.id
INNER JOIN    program_component_type_rel AS pctr
ON            p.id = pctr.program_ID
WHERE
AND        pctr.is_group = 1
AND        pctr.is_individual = 1
ORDER BY     p.name, p.city, p.prov_state, p.country