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#">
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>