[thelist] SQL: Eliminating Duplicates with .getRows()

Ken Kogler ken.kogler at curf.edu
Mon Sep 2 16:05:01 CDT 2002


I'm using ASP's .getRows() thingy, but this is more of a SQL question
than anything else.

I'm trying to eliminate the duplicate records returned from a recordset.
Here's my situation (stay with me, this gets a bit rough:)

I've got 2 tables -- one for safety signs, and one for the categories
each sign belongs to. For example, I've got an "Electrical Hazard" sign,
and it belongs to the "electrical", "laser", "shock", and "hazard"
categories.

Simple enough, right? Well, my problem is this:

I'm writing a search engine for the site, and if I were to search for
"electrical", that sign would come back in the results twice -- once
because the word "electrical" is in the title, and once because it's in
one of the categories.

I know WHY this is happening, but I can't seem to wrap my brain around
the logic to prevent it. Here's my SQL:

  SELECT sID, sNumber, sTitle, sOshaAnsi, aApp
  FROM tblSigns, xrefAppsSigns
  WHERE (sTitle LIKE '% s %'
    OR aApp LIKE '% s %')
    AND sNumber = aNumber
(the "s" represents the search term, in this case "electrical")

I know the problem is with the "OR" statement, but there's a good reason
it's there -- if I use "AND", then only signs where the search term is
in the category AND in the sign's title get returned, and that's no
good... I want the accuracy of using the "OR" statement, but without the
nasty duplicates it returns.

Thoughts?

--Ken




More information about the thelist mailing list