View or join querey with one column aggregated?

[Ruairi]

I am somewhat new to SQL Server(6.5) and it doesnt seem like it's possible to do what i want to do. Hopefully i'm wrong. For a typical report in my VB application i need to access information from 4 to 6 tables. From most of these tables all i need is to get one piece of info pointed to by a foreign key column in the load table. For example a load has columns vendorrec, sortsrec, destinationrec and from the vendor, sort, and destination tables i simply need to join to the name column in those tables. That part is easy enough. There is also a logs table. for one record in the load table there might be hundreds of log records, or there might be none. All i need to know from the log table is whether or not there are any logs for a given load record. I know how to formulate the outer join querey to return (null) if there are no log records for a load and return a record for each log record if there are logs. what i would like to be able to do is return MIN() of the log rec column in a join querey, but to do that all the columns have to be aggregated or grouped by. With the number of columns in a typical querey this results in running out of worktables. Does anyone know of a way to create a view or some other kind of a querey that will return a record for each load&nbsp;&nbsp;record, with the first log record associated with that load (each log record has a column loadsrec, which is a foreign key to the rec column in the loads table) or (null) if there are none. I have only talked about a few representative columns i need to get info from to keep this question as simple as possible. There are actually about 15 -20 columns in an average querey, most of them from the loads table. so trying to do a join querey and group by every column will not work. Thank you in advance for any help. <br>

 

[MalcolmW]

Ruairi,<br>Could you post a sample table def, along with a sample query, which briefly explains what you want to do?&nbsp;&nbsp;I find paragraphs longer than my short term memory difficult to deal with <br><br>I think I have a solution for you, but it is easier to explain it with SQL. <br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Ruairi]

TABLES:<br><br>Vendor<br>Rec <br>Name<br><br>Sorts<br>Rec <br>Abbrev <br><br>Destination<br>Rec <br>Name <br><br>Loads<br>Rec<br>Vendorrec (foreign key to Vendor.Rec) <br>Sortsrec&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(foreign key to Sorts.Rec)<br>Destinationrec (foreign key to destination.Rec) <br>Net <br>Gross<br>Util<br>Pgross<br>Statusrec <br>Samplescaledflag<br>Weighedflag<br>Mtflag<br>Wieght <br><br>Logs<br>Rec<br>Loadsrec (foreign key to Loads.rec) <br><br>Heres what I would like to do. I don’t think I can because of the rule that if one column is aggregated they all must be aggregated or grouped. Grouping by all these columns causes too many work tables to be needed And the querey fails. <br><br>Select “vendor”=V.name, “sort’=S.name, ‘Dest’=D.name, ‘lrec’=LG.rec, ‘rec’=LD.rec, ‘vrec’=LD.vendorrec, ‘srec’=LD.sortsrec, ‘drec’=LD.destinationrec, ‘net’=LD.net, ‘gross’=LD.gross, ‘util’=LD.util, ‘Pgross’=LD.pgross, ‘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><br>Thanks in advance for any help with this, or just for trying. <br><br>Ruairi<br>

 

[MalcolmW]

just add to the end<br>WHERE NOT EXISTS (SELECT *<br>FROM #Logs AS LG1<br>WHERE LG1.rec &lt; LG.rec)<br>This will return the minimum record - it can be modified fairly easily to return the min record for a group as well.<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>