Help with complex SQL query
I have a query where I am using INNER JOIN's to grab info from a main table and a bunch of reference tables. It goes as follows: I have a main "seeker" table, and a bunch of reference tables like state, size, number of offices, etc...
There is a search field called 'industry' (master list of industries in a seperate table called 'tbl_cs_industry'), passed to the search query as 'sr_industry'. When the seekers create their profile, they can select multiple industries and those are stores in a secondary table, 'tbl_cs_seeker_in'. Even though a seeker can have multiple industries, when the search is performed... they can only select ONE industry from the drop-down.
There is a search field called 'industry' (master list of industries in a seperate table called 'tbl_cs_industry'), passed to the search query as 'sr_industry'. When the seekers create their profile, they can select multiple industries and those are stores in a secondary table, 'tbl_cs_seeker_in'. Even though a seeker can have multiple industries, when the search is performed... they can only select ONE industry from the drop-down.
<cfquery name="get" datasource="#request.dsn#">
SELECT tbl_cs_seeker.*,tbl_stateprov.stprv_ID,tbl_stateprov.stprv_name,tbl_cs_yearsinbus.*,tbl_cs_spacesize.*,tbl_cs_numoffices.*,tbl_cs_leaseterm.*,tbl_cs_seeker_in.*
FROM tbl_cs_seeker
INNER JOIN tbl_stateprov ON tbl_cs_seeker.skr_stateSeek = tbl_stateprov.stprv_ID
INNER JOIN tbl_cs_yearsinbus ON tbl_cs_seeker.skr_yearsinbus = tbl_cs_yearsinbus.ID
INNER JOIN tbl_cs_spacesize ON tbl_cs_seeker.skr_sqFt = tbl_cs_spacesize.ID
INNER JOIN tbl_cs_numoffices ON tbl_cs_seeker.skr_numOffices = tbl_cs_numoffices.ID
INNER JOIN tbl_cs_leaseterm ON tbl_cs_seeker.skr_leaseTerm = tbl_cs_leaseterm.ID
INNER JOIN tbl_cs_seeker_in ON tbl_cs_seeker.skr_ID = tbl_cs_seeker_in.in_ID AND
WHERE tbl_cs_seeker.skr_status = 1
AND tbl_cs_seeker.skr_stateSeek = <cfqueryparam value="#form.sr_state#" cfsqltype="cf_sql_integer">
AND tbl_cs_seeker.skr_citySeek LIKE <cfqueryparam value="%#form.sr_city#%" cfsqltype="cf_sql_varchar">
<!--- supporting years in business --->
<cfif form.sr_years GTE 1>
AND tbl_cs_seeker.skr_yearsinbus LIKE <cfqueryparam value="#form.sr_years#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting space in square feet --->
<cfif form.sr_space GTE 1>
AND tbl_cs_seeker.skr_sqFt LIKE <cfqueryparam value="#form.sr_space#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting number of workstations --->
<cfif form.sr_workstations GTE 1>
AND tbl_cs_seeker.skr_numOffices LIKE <cfqueryparam value="#form.sr_workstations#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting lease term --->
<cfif form.sr_lease GTE 1>
AND tbl_cs_seeker.skr_leaseTerm LIKE <cfqueryparam value="#form.sr_lease#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting rent price --->
<cfif form.sr_rent GTE 1>
AND tbl_cs_seeker.skr_rr2 >= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer">
AND tbl_cs_seeker.skr_rr1 <= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer">
<!--- supporting industry info --->
<cfif form.sr_industry GTE 1>
AND tbl_cs_seeker_in.in_ID = <cfqueryparam value="#form.sr_industry#" cfsqltype="cf_sql_integer"></cfif>
</cfif>
</cfquery>
bitref
Then, what is your request? What do you need to do?
you had an AND at the end of the
INNER JOIN tbl_cs_seeker_in ON tbl_cs_seeker.skr_ID = tbl_cs_seeker_in.in_ID
line ....
do you really need the data from the seeker_in table?
could it become
<cfquery name="get" datasource="#request.dsn#"
SELECT tbl_cs_seeker.*,tbl_statep
,tbl_cs_spacesize.*,tbl_cs
FROM tbl_cs_seeker
INNER JOIN tbl_stateprov ON tbl_cs_seeker.skr_stateSee
INNER JOIN tbl_cs_yearsinbus ON tbl_cs_seeker.skr_yearsinb
INNER JOIN tbl_cs_spacesize ON tbl_cs_seeker.skr_sqFt = tbl_cs_spacesize.ID
INNER JOIN tbl_cs_numoffices ON tbl_cs_seeker.skr_numOffic
INNER JOIN tbl_cs_leaseterm ON tbl_cs_seeker.skr_leaseTer
WHERE tbl_cs_seeker.skr_status = 1
AND tbl_cs_seeker.skr_stateSee
AND tbl_cs_seeker.skr_citySeek
<!--- supporting years in business --->
<cfif form.sr_years GTE 1>
AND tbl_cs_seeker.skr_yearsinb
<!--- supporting space in square feet --->
<cfif form.sr_space GTE 1>
AND tbl_cs_seeker.skr_sqFt LIKE <cfqueryparam value="#form.sr_space#" cfsqltype="cf_sql_integer"
<!--- supporting number of workstations --->
<cfif form.sr_workstations GTE 1>
AND tbl_cs_seeker.skr_numOffic
<!--- supporting lease term --->
<cfif form.sr_lease GTE 1>
AND tbl_cs_seeker.skr_leaseTer
<!--- supporting rent price --->
<cfif form.sr_rent GTE 1>
AND tbl_cs_seeker.skr_rr2 >= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer"
AND tbl_cs_seeker.skr_rr1 <= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer"
<!--- supporting industry info --->
<cfif form.sr_industry GTE 1>
AND tbl_cs_seeker.skr_ID = <cfqueryparam value="#form.sr_industry#"
</cfif>
</cfquery>
INNER JOIN tbl_cs_seeker_in ON tbl_cs_seeker.skr_ID = tbl_cs_seeker_in.in_ID
line ....
do you really need the data from the seeker_in table?
could it become
<cfquery name="get" datasource="#request.dsn#"
SELECT tbl_cs_seeker.*,tbl_statep
,tbl_cs_spacesize.*,tbl_cs
FROM tbl_cs_seeker
INNER JOIN tbl_stateprov ON tbl_cs_seeker.skr_stateSee
INNER JOIN tbl_cs_yearsinbus ON tbl_cs_seeker.skr_yearsinb
INNER JOIN tbl_cs_spacesize ON tbl_cs_seeker.skr_sqFt = tbl_cs_spacesize.ID
INNER JOIN tbl_cs_numoffices ON tbl_cs_seeker.skr_numOffic
INNER JOIN tbl_cs_leaseterm ON tbl_cs_seeker.skr_leaseTer
WHERE tbl_cs_seeker.skr_status = 1
AND tbl_cs_seeker.skr_stateSee
AND tbl_cs_seeker.skr_citySeek
<!--- supporting years in business --->
<cfif form.sr_years GTE 1>
AND tbl_cs_seeker.skr_yearsinb
<!--- supporting space in square feet --->
<cfif form.sr_space GTE 1>
AND tbl_cs_seeker.skr_sqFt LIKE <cfqueryparam value="#form.sr_space#" cfsqltype="cf_sql_integer"
<!--- supporting number of workstations --->
<cfif form.sr_workstations GTE 1>
AND tbl_cs_seeker.skr_numOffic
<!--- supporting lease term --->
<cfif form.sr_lease GTE 1>
AND tbl_cs_seeker.skr_leaseTer
<!--- supporting rent price --->
<cfif form.sr_rent GTE 1>
AND tbl_cs_seeker.skr_rr2 >= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer"
AND tbl_cs_seeker.skr_rr1 <= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer"
<!--- supporting industry info --->
<cfif form.sr_industry GTE 1>
AND tbl_cs_seeker.skr_ID = <cfqueryparam value="#form.sr_industry#"
</cfif>
</cfquery>
<cfquery name="get" datasource="#request.dsn#">
SELECT tbl_cs_seeker.*,tbl_stateprov.stprv_ID,tbl_stateprov.stprv_name,tbl_cs_yearsinbus.*
,tbl_cs_spacesize.*,tbl_cs_numoffices.*,tbl_cs_leaseterm.*,tbl_cs_seeker_in.*
FROM tbl_cs_seeker
INNER JOIN tbl_stateprov ON tbl_cs_seeker.skr_stateSeek = tbl_stateprov.stprv_ID
INNER JOIN tbl_cs_yearsinbus ON tbl_cs_seeker.skr_yearsinbus = tbl_cs_yearsinbus.ID
INNER JOIN tbl_cs_spacesize ON tbl_cs_seeker.skr_sqFt = tbl_cs_spacesize.ID
INNER JOIN tbl_cs_numoffices ON tbl_cs_seeker.skr_numOffices = tbl_cs_numoffices.ID
INNER JOIN tbl_cs_leaseterm ON tbl_cs_seeker.skr_leaseTerm = tbl_cs_leaseterm.ID
INNER JOIN tbl_cs_seeker_in ON tbl_cs_seeker.skr_ID = tbl_cs_seeker_in.in_ID
WHERE tbl_cs_seeker.skr_status = 1
AND tbl_cs_seeker.skr_stateSeek = <cfqueryparam value="#form.sr_state#" cfsqltype="cf_sql_integer">
AND tbl_cs_seeker.skr_citySeek LIKE <cfqueryparam value="%#form.sr_city#%" cfsqltype="cf_sql_varchar">
<!--- supporting years in business --->
<cfif form.sr_years GTE 1>
AND tbl_cs_seeker.skr_yearsinbus LIKE <cfqueryparam value="#form.sr_years#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting space in square feet --->
<cfif form.sr_space GTE 1>
AND tbl_cs_seeker.skr_sqFt LIKE <cfqueryparam value="#form.sr_space#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting number of workstations --->
<cfif form.sr_workstations GTE 1>
AND tbl_cs_seeker.skr_numOffices LIKE <cfqueryparam value="#form.sr_workstations#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting lease term --->
<cfif form.sr_lease GTE 1>
AND tbl_cs_seeker.skr_leaseTerm LIKE <cfqueryparam value="#form.sr_lease#" cfsqltype="cf_sql_integer"></cfif>
<!--- supporting rent price --->
<cfif form.sr_rent GTE 1>
AND tbl_cs_seeker.skr_rr2 >= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer">
AND tbl_cs_seeker.skr_rr1 <= <cfqueryparam value="#form.sr_rent#" cfsqltype="cf_sql_integer">
<!--- supporting industry info --->
<cfif form.sr_industry GTE 1>
AND tbl_cs_seeker_in.in_ID = <cfqueryparam value="#form.sr_industry#" cfsqltype="cf_sql_integer"></cfif>
</cfif>
</cfquery>ASKER
Hey, thanks for taking a look! That extra AND was actually there by accident and caught after p-sting this but before you worte this! It is gone... YES, I do need the info from the 'tbl_cs_seeeker_in' table... the main table, 'tbl_cs_seeker' does not have any references or fields that relate to the selected industries (unlike all of the other fields). The search field 'sr_industry' passes the ID number of the selected industry to the query. The query should look up all of the entries in the 'tbl_cs_seeeker_in' table that relate to this specific 'skr_ID' AND then only get the one that has the industry ID of the passed search parmaeter. Make sense?
ASKER
Just a though as I try different things... perhaps I should be using a different kind of "JOIN"?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
my own solution...