Bind (SQL in Coldfusion) 1

[JohnandSwifty]

This is a SQL query really but im sure someone here will know...

I have a result set for customers details, there are two fields that relate to groups that the customer belongs to. These fields are codes that i then look up in the groups table to find the group name. Can anyone tell me if/ how to put all three below in the same query?...(I'm finding the BIND etc a little confusing)

<cfquery datasource="CRM" name="GetContacts"
SELECT
Code,
ContCat02,
Name,
Group01
FROM Contacts
</cfquery>

<cfquery datasource="CRM" name="GetClassGroups">
SELECT
Code,
Name,
Description
FROM ClassGroups
WHERE Code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#GetContacts.Group01#">
</cfquery>

<cfquery datasource="CRM" name="GetClassContCat02">
SELECT
Code,
Name,
Description
FROM ClassContCat02
WHERE Code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#GetContacts.ContCat02#">
</cfquery>

 

[Sarky78]

You could do the following:

<cfquery datasource="CRM" name="GetContacts"
SELECT Code, ContCat02, Name, Group01
FROM ((Contacts LEFT JOIN ClassGroups ON Contacts.Group01=ClassGroups.Code) LEFT JOIN ClassContCat02 ON Contacts.ContCat02=ClassContCat02)
</cfquery>

You'll need to put all of the fields that you want to in the select statement and I'd recommend using alias' for the table names to make things readable and easier to debug later.

I've used a left join here, but if there are always going to be records in the ClassGroups and ClassContCat02 tables then you can change the left joins to inner joins

Hope this helps!

Tony

 

[JohnandSwifty]

That works great - thanks!