[thelist] SQL: Eliminating Duplicates with .getRows()
rudy
r937 at interlog.com
Mon Sep 2 22:28:00 CDT 2002
>>yes, but that's what i meant -- if the search term is found in the
>>category, then you're saying you want all the signs in that
>>category returned...
>
>Yes. Strange, but my well-informed client wants it that way.
okay, try this --
select sID, sNumber, sTitle, sOshaAnsi, aApp
from xrefAppsSigns
inner
join tblSigns
on aNumber = sNumber
where aApp LIKE '% s %'
union
select sID, sNumber, sTitle, sOshaAnsi, aApp
from tblSigns
where sTitle LIKE '% s %'
the first select finds all categories that meet the search criteria, and
pulls out all their signs
the second select finds all signs that meet the search criteria
UNION -- without the ALL -- takes care of removing duplicates
did you want those spaces in the search strings? because that will not
find the s if it's at the beginning or end of the field
rudy
More information about the thelist
mailing list