sql - Select records / count distinct from another table -

i using coldfusion , sql server manage hockey stats. have sql query performing poorly because run query within actual loop return results. know no , performance lagging. hoping can me right.

my first sql looks this:

    select     s.gameid,     s.leagueid,     s.seasonid,      s.dateplayed,      s.timeplayed,      s.hometeamid,      s.visitorteamid,      hometeam.teamname hometeamname,      visitorteam.teamname visitorteamname      schedules s      join teams hometeam on s.hometeamid = hometeam.teamid        join teams visitorteam on s.visitorteamid = visitorteam.teamid      s.leagueid = <cfqueryparam value="#application.leagueid#" cfsqltype="cf_sql_integer">     , s.seasonid = <cfqueryparam value="#application.seasonid#" cfsqltype="cf_sql_integer">     order s.gameid desc 

this runs pretty fast , that. admins want show of scheduled games have stats assigned them. cfoutput query , within loop, run query uses gameid check gamestats table.

    <cfoutput query="qlistgames">     *** table stuff here...     <cfquery name="qcheckstats" datasource="#appication.dsn#">     select gameid      dbo.gamestats     gameid = #qlistgames.gameid#     </cfquery>     <cfif isdefined("qassigned.recordcount") , qassigned.recordcount gt "0">     true     <cfelse>     false     </cfif>      </cfoutput> 

this of course causes major lag there 100s of records , hitting database on , over. ideally, run single sql statement can gather of records need show, check gamestats table correlating record. need show true if gamestats found or false if not. cannot wrap head around sql needed make happen.

can advise me? thank you.

you need outer join (often abbreviated left join or right join) gamestats table. there several ways data; select gamestats.gameid using column alias (to avoid conflicting schedules.gameid) , check whether null or has value in cfoutput. or select output need directly in query , remove logic loop entirely:

select     s.gameid,     s.leagueid,     s.seasonid,      s.dateplayed,      s.timeplayed,      s.hometeamid,      s.visitorteamid,      hometeam.teamname hometeamname,      visitorteam.teamname visitorteamname,     case when gs.gameid null 'false' else 'true' end hasgamestats schedules s inner join teams hometeam on s.hometeamid = hometeam.teamid    inner join teams visitorteam on s.visitorteamid = visitorteam.teamid     left join gamestats gs on s.gameid = gs.gameid s.leagueid = <cfqueryparam value="#application.leagueid#" cfsqltype="cf_sql_integer">     , s.seasonid = <cfqueryparam value="#application.seasonid#" cfsqltype="cf_sql_integer"> order s.gameid desc