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

Joshua Olson joshua at waetech.com
Tue Sep 3 07:46:01 CDT 2002


----- Original Message -----
From: "Ken Kogler" <ken.kogler at curf.edu>
Sent: Monday, September 02, 2002 11:41 PM


> SQL looks like a work of art, Rudy, but it appears as if Access doesn't
> support the join... Is there another way to do it? In my original post,
> I had something to the effect of:

Since you are using Access, have you re-looked at the putting the select
query within the IN clause, such as this:

SELECT fields
FROM table
WHERE sID IN
(
  [a select that grabs the sIDs]
)

That query will remove the duplicates as well.

In your case, something like:

select sID, sNumber, sTitle, sOshaAnsi, aApp
from xrefAppsSigns
      , tblSigns
where sID IN
(
   select sID
     from xrefAppsSigns
        , tblSigns
    where aNumber = sNumber
      and aApp LIKE '%s%'
   union
   select sID
     from tblSigns
        , xrefAppsSigns
    where sNumber = aNumber
      and sTitle LIKE '%s%'
)
and sNumber = aNumber

-joshua




More information about the thelist mailing list