Attn. MalcomW: that doesnt seem to work, does this look right?

[Ruairi]

Heres the querey i ran using the where clause you suggested<br><br>Select 'vendor'=V.name, 'sort'=S.name, 'Dest'=D.name, 'lrec'=LG.rec, 'rec'=LD.rec, 'vrec'=LD.vendorrec, <br>'srec'=LD.sortsrec, 'drec'=LD.destinationrec, 'net'=LD.net, 'gross'=LD.gross, 'util'=LD.utility, 'Pgross'=LD.pgross, <br>'status'=LD.statusrec, 'sampled'=LD.samplescaledflag, 'weighed'=LD.weighedflag, 'mt'=LD.mtflag, 'weight'=LD.weight<br>FROM loads as LD <br>Join vendor as V on (LD.vendorrec = V.rec) <br>Join sorts as S on (LD.sortsrec = S.rec) <br>Join destination as D on (LD.destinationrec = D.rec) <br>Full Outer Join logs as LG on (LD.rec = LG.loadsrec)<br>WHERE NOT EXISTS (SELECT *<br>FROM logs AS LG1<br>WHERE LG1.rec &lt; LG.rec) and LD.dag &gt;= '04/01/2000' and LD.dag &lt;= '04/15/00' <br><br>it seems to return rows only for loads with no logs. <br>the entire result set for this querey has only (null) for lrec.Here are 2 columns from the result set of this querey.<br><br>lrec&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rec&nbsp;&nbsp;&nbsp;<br>----------- -----------<br>(null)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;90581&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>(null)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;90647<br><br>I did a seperate querey(the same one as above without the WHERE NOT EXISTS part) and limited the range to LD.rec &gt; 90581 and LD.rec &lt; 90647. This returned 65 loads with logs between these 2 loads. Am i somehow misusing the quereyy you suggested? Thank You, <br>Ruairi

 

[MalcolmW]

I think we're close.<br>You do have a full outer join with Logs, which I don't understand.&nbsp;&nbsp;If you don't want Loads that have no associated Logs, you should probably change this join.&nbsp;&nbsp;Do you need both sides preserved in this join?<br><br>Just as a tip - incorporating DDL instead of samples and narrative descriptions helps quite a bit to duplicate your scenario.<br>ie <br>CREATE TABLE Logs (rec char(4), etc)<br>INSERT Logs VALUES('ABCD', etc)<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Ruairi]

the reason for the full outer join is that i DO want loads with no logs preserved. If a load has no logs i want to see (null) for lrec, if the load does have logs i want lrec to be the record number of the first log for that load.

 

[MalcolmW]

Ok - I have no way of checking this because I don't have the tables, but..<br>WHERE NOT EXISTS (SELECT *<br>FROM logs AS LG1<br>WHERE LG1.rec &lt; LG.rec) <br>returns the min log record for the dataset, which happens to be null, and also happens to be a tie cause you get multiple rows returned.<br>So<br>WHERE NOT EXISTS (SELECT *<br>FROM logs AS LG1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FULL OUTER JOIN Loads AS LD1 ON LD1.rec = LG1.loadsrec<br>WHERE LG1.rec &lt; LG.rec<br>AND LD1.rec = LD.rec) <br>I would have to create sample tables and populate them with some data to check this though.&nbsp;&nbsp;I don't want to post &quot;solutions&quot; that don't work, but if you would create the DDL, I'll test it. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Ruairi]

malcom, <br>that looks like it works perfectly. ive got result sets with one record per load whether lrec be (null) or one log record. Thank you very much for your help. <br>Thanks, <br>&nbsp;&nbsp;Ruairi