Duplicates Returning in SQL Query

stanleyc · #1 (**permalink**) 04-05-11, 00:31

I have the following query and I am getting duplicates that I can't get rid of with Distinct etc. I have searched and searched and can't get any fix for this!!! ANY help would be greatly appreciated.

Date 1, Date2 and Unit1 and Unit2 are values being sent in a URL from an ASP page.

Chris

Here's the statement:

Quote:

SELECT Active_Calls.Call_Number, Active_Calls.Actual_Incid_Location, Active_Calls.First_Unit, Callunitdata.Unit, Active_Calls.Call_Taker, Active_Calls.Jurisdiction, Active_Calls.Date_Received, Active_Calls.Call_Class, Active_Calls.Actual_Incid_City, Active_Calls.Complaint, Active_Calls.Landmark FROM dbo.Active_Calls AS Active_Calls INNER JOIN dbo.CALLUNITDATA AS Callunitdata ON Active_Calls.Call_Number = Callunitdata.Call_Number WHERE Active_Calls.Date_Received BETWEEN Date1 AND Date2 AND Active_Calls.Jurisdiction = 'test' AND (Callunitdata.Unit LIKE %Unit1% OR Unit2 is null) ORDER BY Active_Calls.Call_Number ASC

r937 · #2 (**permalink**) 04-05-11, 05:10

two comments

first, the result set cannot possibly contain duplicates when using DISTINCT

second, the query you posted cannot possibly execute, because it contains a glaring syntax error

if you would like to try again and restate your problem? perhaps giving more information?

stanleyc · #3 (**permalink**) 04-05-11, 08:02

This is a statement that is in an ASP page that I created to query a SQL Database. I'm sorry if I left out something to give it a syntax error but it does run, I just copied the statement from my page. Anyway the results show a list of calls for a fire and ems service based on input from the user from a form on another asp page. The user puts in a from and to date and they can enter a unit in another field if they want and when they do that everything is fine because it filters everything else out but when they leave the unit off and just search from and to dates it displays all calls for that time frame and duplicates that are in the database. If there is a call in there with 2 different Units on it (CALLUNITDATA.Unit) then it lists the same location, call# etc in the table view that I have created 2 times and the only difference is the unit that's coming from the CALLUNITDATA table. If there is 4 units on that call it displays 4 different calls in the results and so on. I just want 1 of those calls displayed.

Thanks
CS

r937 · #4 (**permalink**) 04-05-11, 09:49

your syntax error was here --

Code:

Callunitdata.Unit LIKE %Unit1%

you couldn't just have "copied the statement from your page" because copy does not selectively drop the quotes

so what you surely had was

Code:

Callunitdata.Unit LIKE '%Unit1%'

now, if Unit1 is a parameter, and it's left blank, then this reduces to

Code:

Callunitdata.Unit LIKE '%%'

which of course is true for every row, except rows where Callunitdata.Unit itself is null

how to fix this?

easy -- you're using ASP, so use it to detect when Unit1 is null, and then omit that test from the query

stanleyc · #5 (**permalink**) 04-05-11, 12:27

Let me just throw in there that this code was mostly created in Dreamweaver, yeah i know, and I know I didnt clarify this earlier which I should have but in the code Unit1 and Unit2 are actually the same thing. I have those setup as parameters, there is one field along with the from and to date fields for the user to put in a unit. If the unit entered and is LIKE Unit1 then it uses that otherwise if it's left blank in the parameter Unit2 is actually setup as the same field in the table as Unit but since it's blank I have the OR Unit2 is null in there so that if a user doent put in a unit at all it will still run the query with just the date ranges and when that happens that's where I get the duplicates. So from your last response I still don't understand what I'm supposed to do to have the ASP to detect when Unit1 is null, and then omit that test from the query??

CS

r937 · #6 (**permalink**) 04-05-11, 17:54

Quote:

Originally Posted by stanleyc

...I still don't understand what I'm supposed to do to have the ASP to detect when Unit1 is null, and then omit that test from the query??

perhaps you could ask one of the moderators (by clicking on the little icon next to the post number at the top right of a post) to move this thread to the asp forum so that an asp programmer can advise you

http://www.dbforums.com/db_images_v3...ons/report.gif

rokslide · #7 (**permalink**) 04-06-11, 22:26

let me see if I can help here....

It sounds like you have a form with 3 fields, a from date a to date and a unit.

If they enter something in all 3 fields you want to use them all, but if they don't enter a unit then you want to drop that and just select based on the date range right??

so something like this....

Code:

Dim fromDate, toDate, units, sqlString
fromDate = Request.Form["txtFromDate"]
toDate = Request.Form["txtToDate"]
units = Request.Form["txtUnits"]

sqlString = "SELECT Active_Calls.Call_Number, Active_Calls.Actual_Incid_Location, Active_Calls.First_Unit, Callunitdata.Unit, Active_Calls.Call_Taker, Active_Calls.Jurisdiction, Active_Calls.Date_Received, Active_Calls.Call_Class, Active_Calls.Actual_Incid_City, Active_Calls.Complaint, Active_Calls.Landmark FROM dbo.Active_Calls AS Active_Calls "

if(not(isnull(units))) then
    sqlString = sqlString + "INNER JOIN dbo.CALLUNITDATA AS Callunitdata ON Active_Calls.Call_Number = Callunitdata.Call_Number "
end if

sqlString = sqlString +"WHERE Active_Calls.Date_Received BETWEEN Date1 AND Date2 AND Active_Calls.Jurisdiction = 'test' "

if(not(isnull(units))) then
    sqlString = sqlString +"AND (Callunitdata.Unit LIKE %Unit1% OR Unit2 is null) " 
end if

sqlString = sqlString +"ORDER BY Active_Calls.Call_Number ASC "

If you post more of your ASP code I can probably help a lot more, but going with what you have supplied this is the best I can do for you.

Internet Services

Web Development

Digital Marketing

Consumer Tech