Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2011
    Posts
    13

    Unanswered: Duplicates Returning in SQL Query

    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:

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2011
    Posts
    13
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2011
    Posts
    13
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stanleyc View Post
    ...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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •