Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

SQL query with multiple table join

I have a query I am writing... It hits a "listings" table fo members.  I can search by state, city and 'services offered'.  I have the state/city search returning results perfectly... code attached below.  Listing table has a state ID field... and references the state entry in a seperate table.  Very simple and straight forward.

Problem I am having... I now need to further the query and see what services are offered by the merchant listings.  The listings table does not have any reference to the services offered (services are housed in a seperate table 'cs_tbl_services').  The references to the services offered by  the merchant listing are housed in yet another table, that one named 'tbl_cs_merchant_svcs'.  This reference table has a listingID as well as a serviceID.   How do I write my SQL join query, for what appears to be a 3 table join?
<!--- get the info for listings --->
<cfquery name="get" datasource="#request.dsn#">
SELECT tbl_cs_merchant.*,tbl_stateprov.*
FROM tbl_cs_merchant
INNER JOIN tbl_stateprov ON tbl_cs_merchant.lst_locState = tbl_stateprov.stprv_ID

WHERE tbl_cs_merchant.lst_status = 1 
<cfif form.sr_state NEQ ''>AND tbl_cs_merchant.lst_locState = <cfqueryparam value="#form.sr_state#" cfsqltype="cf_sql_integer"></cfif>
<cfif form.sr_city NEQ ''>AND tbl_cs_merchant.lst_locCity LIKE <cfqueryparam value="%#form.sr_city#%" cfsqltype="cf_sql_varchar"></cfif>

</cfquery>

Open in new window

0
14_east
Asked:
14_east
1 Solution
 
tim_csCommented:
Give this a try.  If all of your listing records don't always have a merchant or service then you can switch to left joins.  
SELECT tbl_cs_merchant.*,tbl_stateprov.*, cs_tbl_services.*
FROM tbl_cs_merchant 
INNER JOIN tbl_stateprov ON tbl_cs_merchant.lst_locState = tbl_stateprov.stprv_ID 
INNER JOIN tbl_cs_merchant_svcs ON tbl_cs_merchant.ListingID =  tbl_cs_merchant_svcs.ListingID
INNER JOIN cs_tbl_services ON tbl_cs_merchant_svcs.ServiceID = cs_tbl_services.ServiceID
 
WHERE tbl_cs_merchant.lst_status = 1  
<cfif form.sr_state NEQ ''>AND tbl_cs_merchant.lst_locState = <cfqueryparam value="#form.sr_state#" cfsqltype="cf_sql_integer"></cfif> 
<cfif form.sr_city NEQ ''>AND tbl_cs_merchant.lst_locCity LIKE <cfqueryparam value="%#form.sr_city#%" cfsqltype="cf_sql_varchar"></cfif>

Open in new window

0
 
_agx_Commented:
(no points...)

> SELECT tbl_cs_merchant.*,tbl_stateprov.*, cs_tbl_services.*

Try not to use SELECT * in your queries because cfquery may get confused if it hits multiple columns with the same name.
0
 
14_eastAuthor Commented:
Thanks fo rthe help... I tweaked it a little to get just what I needed...  check out the attached code.
<cfquery name="get" datasource="#request.dsn#">	
	SELECT tbl_cs_merchant.*,tbl_stateprov.*<cfif form.sr_services NEQ 0>,tbl_cs_services.*</cfif>
	FROM tbl_cs_merchant 
	INNER JOIN tbl_stateprov ON tbl_cs_merchant.lst_locState = tbl_stateprov.stprv_ID 
	  <cfif form.sr_services NEQ 0>INNER JOIN tbl_cs_merchant_svcs ON tbl_cs_merchant.lst_ID =  tbl_cs_merchant_svcs.lst_ID</cfif>
	    <cfif form.sr_services NEQ 0>INNER JOIN tbl_cs_services ON tbl_cs_merchant_svcs.lst_ID = tbl_cs_services.ID</cfif>
	 
	WHERE tbl_cs_merchant.lst_status = 1
	AND  tbl_cs_merchant.lst_xpiryDate > <cfqueryparam value="#today#" cfsqltype="cf_sql_date">
	<cfif form.sr_state NEQ ''>AND tbl_cs_merchant.lst_locState = <cfqueryparam value="#form.sr_state#" cfsqltype="cf_sql_integer"></cfif> 
	<cfif form.sr_city NEQ ''>AND tbl_cs_merchant.lst_locCity LIKE <cfqueryparam value="%#form.sr_city#%" cfsqltype="cf_sql_varchar"></cfif>
	<cfif form.sr_services NEQ 0>AND tbl_cs_merchant_svcs.svcs_ID = <cfqueryparam value="#form.sr_services#" cfsqltype="cf_sql_integer"></cfif>
</cfquery>

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now