Output problems with Multiple Query Set

Here is what I want to achieve.....
Firstly, I take a query from the round table and check for all empty entries. This is the weekly submisisons table, so basically I am checking for all those who have yet to enter their weekly picks.

So my first Query looks something like this...

<cfquery datasource = "#request.dsn#" name = "usersOutstanding">
SELECT team_name
FROM round_#round_number#
WHERE margin_1 = ''
</cfquery>

Next I want to take all those users who haven't entered anything for this week yet and collect their information from the client table (which initially stored phone numbers, contact details etc upon signup) and output in a table.

<cfquery datasource = "#request.dsn#" name = "clientDetails">
SELECT *
FROM client
WHERE team_name = '#users.team_name#'
</cfquery>


But this only gives me the first users information and I want ALL users from the previous query. I tried using loop but to no avail. All help greatly appreciated.

adieu
Mark

Using queries from queries, I managed to get this working....

<!--- Retrieve all client info --->
<CFQUERY NAME="Clients" DATASOURCE="#request.dsn#">
SELECT *
FROM client
</CFQUERY>

<!--- Retrieve those who failed to submit --->
<CFQUERY NAME="userMargin" DATASOURCE="#request.dsn#">
SELECT team_name
FROM round_#round_number#
WHERE margin_1 = '1-9'
</CFQUERY>

<!--- pull the client info for those who failed --->
<CFQUERY NAME="combined" DBTYPE="query">
SELECT team_name, phone, email
FROM Clients,userMargin
WHERE Clients.team_name=userMargin.team_name
</CFQUERY>


and in the display page

<cfoutput query="combined">
#team_name#, #phone#, #email#<br />
</cfoutput>

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

I guess my question now is this - is there any other way to achieve this using less code?

adieu
Mark

yes, with a join

that's perfect, ty! I did however need to add another column to the round table for #round_number# to make the join process easier.

adieu
Mark