Filtering query with results of another query/repeat region. 1

[RobHat]

Hi guys,

I have a page where I want to display a list of agents within our database, to the right hand side of those agents names I want to have a total number of clients they have registered. I know this should be nothing complicated but for some reason I can't get it to work.

I have this so far:

This code gives me my list of agents:
<cfquery name="rs_viewagent" datasource="mydatasource">
SELECT *
FROM dbo.users_table
WHERE access_level = 'agent'
ORDER BY company ASC
</cfquery>

This should give me my totals:
<cfquery name="rs_clientsregistered" datasource="mydatasource">
SELECT *
FROM dbo.clients_table
WHERE agentID = '#rs_viewagent.username#'
</cfquery>

I then have a standard <cfoutput query="rs_viewagent"> to dump the results down the page, and a record count to display the stats. #rs_clientsregistered.RecordCount#.

When I view this page I get the agents display fine and I get the recordcount from the first person in the query "rs_viewagent" repeated down the page. I know this has something to do with the fact that I need to tell it which person in the db I want stats for for, and that this will have to be dynamicly generated from the repeat region but how. I have searched for ways to link to queryies and so on but cannot find anything that will give the right result. Am I missing something Simple?

Thanks

Rob

 

[cfSearching]

To display the agent names and the total number of clients, try a join instead of separate queries. Using a left join like below forces the query to return all agents, even those with zero clients.

SELECT agt.AgentID, agt.AgentName, COUNT(c.ClientID) AS NumberOfClients
FROM   dbo.users_table AS agt
       LEFT JOIN dbo.clients_table AS c ON agt.AgentID = c.agt.AgentID
WHERE  agt.access_level = 'agent'
GROUP BY agt.AgentID, agt.AgentName
ORDER BY agt.company ASC

----------------------------------

http://cfsearching.blogspot.com/

 

[RobHat]

Hi there thanks for the reply, I have played with this a little to match my column names but I get an error saying:

Invalid column name 'agt'.

The query now looks like:

SELECT agt.ID, agt.username, COUNT(c.ID) AS NumberOfClients
FROM dbo.agents AS agt
LEFT JOIN dbo.clients AS c ON agt.username = c.agt.AgentID
WHERE agt.access_level = 'agent'
GROUP BY agt.AgentID, agt.username
ORDER BY agt.company ASC

exscuse me if I have missed something obviuos as I have never joined tables in queries before.

Regards

Rob

 

[cfSearching]

Are you using the correct column names in your cfoutput? It should be name of the column without the alias "agt". Assuming no typos ..

<cfoutput query="yourQuery">
   #ID# #userName# #NumberOfClients#<br>
</cfoutput>

----------------------------------

http://cfsearching.blogspot.com/

 

[RobHat]

I have just tryed that and double checked the names and so on, here is the error in full.

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'agt'.

The error occurred in path\view_agents.cfm: line 34

32 : </cfif>
33 :
34 : <cfquery name="rs_viewagent" datasource="mydatasource">
35 : SELECT agt.ID, agt.username, COUNT(c.ID) AS NumberOfClients
36 : FROM dbo.agents AS agt


sorry to be a pain.

Rob

 

[cfSearching]

Try this

SELECT agt.company, agt.ID, agt.username, COUNT(c.ID) AS NumberOfClients
FROM   dbo.agents AS agt
       LEFT JOIN dbo.clients AS c ON agt.username = c.AgentID
WHERE  agt.access_level = 'agent'
GROUP BY agt.company, agt.ID, agt.username
ORDER BY agt.company ASC

----------------------------------

http://cfsearching.blogspot.com/

 

[RobHat]

Hi there, thanks for the help. that works great.

Is it possible to get 3 types of results from joing tables like that? my next part of this page displays a total number of clients that have the status "bought".

My query would look something like this:

SELECT ID, agentID
FROM dbo.clients
WHERE agentID = 'dynamicly_generated_agentsID' AND status = 'Bought'

Sorry The reason I did'nt ask this earlier was because I was going to use the same idea from the total clients to display the total clients who bought. Since you cam back with a more streamlined way of doing it I can't figure out how to generate this total.

Thanks Again

 

[cfSearching]

Do you mean in a single query? Maybe, but not if the filters are mutually exclusive.

If not, try modifying the join for each of the three queries. The second would be something like:

SELECT agt.company, agt.ID, agt.username, COUNT(c.ID) AS NumberOfClients
FROM   dbo.agents AS agt
       LEFT JOIN dbo.clients AS c ON agt.username = c.AgentID
WHERE  agt.agentID = 'dynamicly_generated_agentsID' 
AND    agt.status = 'Bought'
GROUP BY agt.company, agt.ID, agt.username
ORDER BY agt.company ASC

----------------------------------

http://cfsearching.blogspot.com/

 

[RobHat]

I have not tried the query you posted just yet, I will try in a min. If you imagine a page with a list of agents. To the right of each agents name is a total number of clients and then a bit further to right is the total of clients who bought.

This is why I was going to originally use 3 queries. one to fetch agent details, one to fetch total clients for that agent and one to fetch total clients from that agent who have bought.

I don't know whether this changes anything as far as how the queries are constructed.

Rob

 

[cfSearching]

So all of the counts would be based on dbo.agent records with an access_level = 'agent'? Then you may be able to use a CASE statement here. Watch out for typos:

SELECT 	agt.company, agt.ID, agt.username, 
	COUNT(c.ID) AS NumberOfClients,
	SUM ( CASE WHEN c.Status = 'Bought' THEN 1 ELSE 0 END ) AS NumberThatBought
FROM   	dbo.agents AS agt
       	LEFT JOIN dbo.clients AS c ON agt.username = c.AgentID
WHERE  	agt.access_level = 'agent'
GROUP BY agt.company, agt.ID, agt.username
ORDER BY agt.company ASC

----------------------------------

http://cfsearching.blogspot.com/

 

[RobHat]

I see what you did, I have placed it in the code and made some small changes but I get this error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'SUM'.

The code is:

SELECT agt.company, agt.ID, agt.username,
COUNT(c.ID) AS NumberOfClients
SUM ( CASE WHEN c.Status = 'Bought' THEN 1 ELSE 0 END ) AS NumberThatBought
FROM dbo.agents AS agt
LEFT JOIN dbo.clients AS c ON agt.username = c.AgentID
WHERE agt.access_level = 'agent'
GROUP BY agt.company, agt.ID, agt.username
ORDER BY agt.company ASC

 

[RobHat]

Forget the last post,it works fine. I missed the coma seperating Count statement from the SUM statement.

Thanks for the help.

Rob

 

[cfSearching]

You are welcome.

----------------------------------

http://cfsearching.blogspot.com/