[thelist] SQL Distinct to 1 item

[j s]

Thanks Brian - I'll work on that. 

Brian Cummiskey <Brian at hondaswap.com> wrote: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.



-- 

* * 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 !