[thelist] SQL Distinct to 1 item

j s jslist at sbcglobal.net
Fri Jun 17 11:15:48 CDT 2005


I got the sql statement to work but it's really slow. The kind of slow where I'm getting gray hair slow.
 
Here's the code:
 
strSQL = "SELECT Vehicle_Files.File_Number,  Employees.LName, Status.Status, Mexican_Cities.City, Mexican_States.State, Adjusters.Insurance_Co, Vehicle_Files.Agent, Vehicle_Files.Opened_Date, Vehicle_Files.Repatriation_Date, Vehicle_Files.File_Status, Vehicle_Files.Status FROM Employees INNER JOIN ((((Vehicle_Files INNER JOIN Status ON Vehicle_Files.Status = Status.Status_ID) INNER JOIN Mexican_Cities ON Vehicle_Files.Location_City = Mexican_Cities.Mex_City_ID) INNER JOIN Mexican_States ON Vehicle_Files.Location_State = Mexican_States.Mex_State_ID) INNER JOIN Adjusters ON Vehicle_Files.Adjuster = Adjusters.Adjuster_ID) ON Employees.Employee_ID = Vehicle_Files.Agent Where (Repatriation_Date BETWEEN #" & txtFrom & "# AND #" & txtTo & "#) ORDER BY Adjusters.Insurance_Co, Vehicle_Files.Status, Repatriation_Date;"
 
Jess

Joshua Olson <joshua at waetech.com> wrote:
> -----Original Message-----
> From: j s
> Sent: Friday, June 17, 2005 9:45 AM
> 
> Hello,
> 
> In ASP I'm using a SQL line that pulls data from a few 
> tables. Is there a way to use Distinct (or something like 
> it) and apply it to only 1 field instead of the whole record? 
> I'm getting duplicates - but if I could distinct on the 
> "File_Number" it would solve my problems.

Jess,

Unfortunately, using distinct takes into account every field, and there's no
way around that.

Looking at your query, it appears that each of the joined tables (Employees,
Mexican_Cities, File_Status, and Adjusters) is related to the main
Vehicle_Files table in a 1-1 relationship and that File_Number is the PK of
the Vehicle_Files table. Is this correct? If it is, then DISTINCT should
be returning only one record for each record in Vehicle_Files.

Would you mind providing a snippet of the resultset (don't include all the
extraneous fields) that shows the issue you are having?

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/


-- 

* * Please support the community that supports you. * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt ! 


More information about the thelist mailing list