Need Help With Outer Join Querey

[Ruairi]

I am trying to use this querey (see below) to select information from the loads table of a database. One of the pieces of information i need about the loads is whether or not there are any logs associated with them. This outer join querey works well for that because i can just check if rs(&quot;lrec&quot IsNull and know if there are logs. The problem is that i just want to retreive one row per load. This row needs to have the first log record number(lrec) for that load if there are logs or (null) if there are no logs. This querey returns all the log recs for loads that have logs and i cant seem to figure out how to have it return just one. This results in recordsets of over 500,000 records being returned to the client (a VB application). The other option of running a stored procedure to check for logs on each load slows the application way down, as does having to deal with these huge recordsets. Any help would be greatly appreciated. <br><br>select 'rec'=LD.rec, 'lrec'=LG.rec<br>from loads as LD <br>full outer join logs as LG ON(LD.rec = LG.loadsrec) <br>where LD.dag &gt;= '01/01/2000' and LD.dag &lt;= '06/01/2000' and LD.statusrec &lt; 2 or (LD.statusrec &gt; 4 and LD.statusrec &lt; 9) <br>and LD.cdatabaserec = 1 and LD.ddatabaserec = 1&nbsp;&nbsp;<br><br>Thanks, <br>&nbsp;&nbsp;&nbsp;Ruairi<br><br>

 

[MalcolmW]

Have you tried aggregating the result set before returning it to your application i.e.<br>select 'rec'=LD.rec, 'lrec'=MIN(LG.rec)<br>from loads as LD <br>full outer join logs as LG ON(LD.rec = LG.loadsrec) <br>where LD.dag &gt;= '01/01/2000' and LD.dag &lt;= '06/01/2000' and LD.statusrec &lt; 2 or (LD.statusrec &gt; 4 and LD.statusrec &lt; 9) <br>and LD.cdatabaserec = 1 and LD.ddatabaserec = 1&nbsp;&nbsp;<br>GROUP BY LD.rec<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>