[thelist] SQL Distinct to 1 item

[Brian Cummiskey]

j s wrote:
> 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.
>  
> Here's the actual string:
> strSQL = "SELECT   Vehicle_Files.File_Number, Vehicle_Files.Agent, Vehicle_Files.Opened_Date, Vehicle_Files.Repatriation_Date, Vehicle_Files.Ins_Claim_Number, Vehicle_Files.OCA, Vehicle_Files.VYR, Vehicle_Files.VMO, Vehicle_Files.VMA, Vehicle_Files.VIN, Vehicle_Files.Theft_Date, Vehicle_Files.Theft_Time, Vehicle_Files.Theft_Address, Vehicle_Files.Theft_City, Vehicle_Files.Theft_State, Vehicle_Files.Theft_Zip, Vehicle_Files.Tow_Location, Vehicle_Files.Repatriation_Cost, Vehicle_Files.NADA_Value, Vehicle_Files.Location_City, Vehicle_Files.Location_State, Vehicle_Files.Vendor, Vehicle_Files.Adjuster, Vehicle_Files.ORI, Vehicle_Files.ORI_Name, Vehicle_Files.File_Status,  Vehicle_Files.Status, Vehicle_Files.Closed_Date,  Vehicle_Files.Salvage_Proceeds, "
>   strSQL = strSQL & "Employees.FName, Employees.LName, "
>   strSQL = strSQL & "Mexican_Cities.City,  "
>   strSQL = strSQL & "File_Status.File_Status, "
>   strSQL = strSQL & "Adjusters.Insurance_Co "
>   strSQL = strSQL & "FROM Vehicle_Files, Employees, Mexican_Cities, File_Status, Adjusters "
>   strSQL = strSQL & "Where  ((Repatriation_Date BETWEEN #" & DateAdd("d",0,txtFrom) & "# AND #" & DateAdd("d",0,txtTo) & "#) AND "
>   strSQL = strSQL & "(Vehicle_Files.Agent = Employees.Employee_ID) AND "
>   strSQL = strSQL & "(Vehicle_Files.Location_City = Mexican_Cities.Mex_City_ID) AND"
>   strSQL = strSQL & "(Vehicle_Files.Adjuster = Adjusters.Adjuster_ID))"
>   strSQL = strSQL & " ORDER BY Adjusters.Insurance_Co, Status, Repatriation_Date;"
>   
>  
> Thanks,
> Jess

Of course you're getting dupes--  you're selecting EVERYTHING from each 
table.

what you need to do is to JOIN the tables on a unique ID that is common 
to all the tables.  if File_Number is in all the tables, LEFT OUTER JOIN 
each table on that field. Thus, getting only one record, but all the 
info from all the tables.

effectively, changing:
strSQL = strSQL & "FROM Vehicle_Files, Employees, Mexican_Cities, 
File_Status, Adjusters "

to

strSQL = strSQL & "FROM Vehicle_Files " &_
	LEFT OUTER JOIN Employees on Employess.File_number = 
Vehicle_Files.File_Number " &_
	LEFT OUTER JOIN Mexican_Cities on Mexican_Cities.File_Number = 
Vhicle_Files.File_Number " &_

and the same for File_Status and Adjusters (sorry, tired of typing...)


if you don't have anything that links the tables together, you need to 
re-build the database and make it relational, or no asp or sql code is 
going to help you really.