query a search query

[cybertek23]

Hi i am doing a site where clients can do a seach based on caseworker name. i need to get the information from 2 tables.



<cfquery name="caseworker2" datasource="webconveyancer" dbtype="ODBC">select caseworkerid from caseworker
where caseworkername like '#choose#%'
</cfquery>

I then use a CF loop to get the information

<cfloop query="caseworker2">


<cfquery name="searchall" datasource="webconveyancer" dbtype="ODBC">
select * from clientdetails
where solicitorid=#solicitorid#
and caseworkerid = #caseworker2.caseworkerid#
order by clientsurname
</cfquery><cfoutput query="searchall">

I then need it to display the results.I need it to use the caseworker id and broker id from the clientdetails tabel to match to the caseworker id and broker id in there respective tables. I have done this like this.

<cfoutput query="searchall"> <tr>
<td><a href="viewclient2.cfm?solicitorid=#solicitorid#&clientid=#clientid#&lnr=#lnr#&choose=#choose#">#clientid#</a></td>
<td>#transaction#</td>
<td>#clientsurname# #clientfirstname#</td>
<td>#clientaddress1#</td>
</cfoutput>
<td><cfquery name="caseworker" datasource="webconveyancer" dbtype="ODBC">
select caseworkername from caseworker where caseworkerid=#searchall.caseworkerid#
</cfquery> <cfoutput query="caseworker">#caseworkername#</cfoutput></td>
<td><cfquery name="broker" datasource="webconveyancer" dbtype="ODBC">
select brokername from broker where brokerid=#searchall.brokerid#
</cfquery> <cfoutput query="broker">#brokername#</cfoutput></td>

The problem is that more that 1 client can have the same caseworker and if the caseworker has been shown it will not reshow it.

Anyideas onhow i can fix this.

[paross1]

Wow, do you have something against writing a query where you join more than one table? Below is just an example, but you might try combining some of your queries into a single query so that all of your required data is in a single record.

SELECT cd.client_id,
cd.solicitorid,
cd.lnr,
cd.transaction,
cd.clientsurname,
cd.clientfirstname,
cd.clientaddress1,
cw.caseworkername,
b.brokername
FROM clientdetails cd
INNER JOIN caseworker cw ON cw.caseworkerid = cd.caseworkerid
INNER JOIN broker ON cd.brokerid = b.brokerid
WHERE cd.solicitorid=#solicitorid#
ORDER BY cd.clientsurname

Phil

[cybertek23]

i have used the attached code but am getting the following error.

Any sugestions on the what the problem is
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'cw.caseworkerid = cd.caseworkerid INNER JOIN broker ON cd.brokerid = b.brokerid'.


SQL = "SELECT cd.client_id, cd.solicitorid, cd.transaction, cd.clientsurname, cd.clientfirstname, cd.clientaddress1, cw.caseworkername, b.brokername FROM clientdetails cd INNER JOIN caseworker ON cw.caseworkerid = cd.caseworkerid INNER JOIN broker ON cd.brokerid = b.brokerid WHERE cd.solicitorid=1 ORDER BY cd.clientsurname"

[paross1]

My bad.... I left off a table alias on the broker table, but you left off two aliases, one on broker the other on caseworker.

<cfquery name="searchall" datasource="webconveyancer" dbtype="ODBC">
SELECT cd.client_id,
cd.solicitorid,
cd.transaction,
cd.clientsurname,
cd.clientfirstname,
cd.clientaddress1,
cw.caseworkername,
b.brokername
FROM clientdetails cd
INNER JOIN caseworker cw ON cw.caseworkerid = cd.caseworkerid
INNER JOIN broker b ON cd.brokerid = b.brokerid
WHERE cd.solicitorid=#solicitorid#
ORDER BY cd.clientsurname
</cfquery>

Or, you can use the pre-ANSI-92 SQL syntax as follows....

<cfquery name="searchall" datasource="webconveyancer" dbtype="ODBC">
SELECT cd.client_id,
cd.solicitorid,
cd.transaction,
cd.clientsurname,
cd.clientfirstname,
cd.clientaddress1,
cw.caseworkername,
b.brokername
FROM clientdetails cd, caseworker cw, broker b
WHERE cd.solicitorid=#solicitorid#
AND cw.caseworkerid = cd.caseworkerid
AND cd.brokerid = b.brokerid
ORDER BY cd.clientsurname
</cfquery>

Phil

[cybertek23 · Accepted solution]

thanks the bottom one pre-ANSI-92 SQL syntax works great