Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011

    Unanswered: Search Joined tables for unique rows

    I have 3 tables:

    SessID PK

    AbstractNo PK
    SessID FK

    AbstractNo PK

    I am currently searching the session table in a form. I want to be able to search the Abstract table by the Abstract.LastName. You can see the relationship.

    My current query is giving me duplicate rows:

    select distinct s.SessId as id, SessionCode as sessionCode, CAST(SessionTitle AS VARCHAR) as sessionTitle,s.TypeCd as typeCode,
    SessDate as date,SC,SORTSC,datename(dw, SessDate) as dayOfWeek,ab.FAInitials, ab.FALastName,auth.LastName as Author, auth.Initials,
    ab.AbstractNo,auth.AbstractNo from SESSION as s
    inner join LUTYPE as t on s.TypeCd=t.TypeCd
    left outer join ABSTRACT ab on s.SessID = ab.SessID
    left outer join AUTHOR auth on ab.AbstractNo = auth.AbstractNo where auth.LastName = 'Wong'
    order by id

    My dupes look like this:
    1, A23, 'topic 1',...........'Wong',E.C
    2, A23, 'topic 1',...........'Wong',E.C
    3, A20, 'topic 2',...........'Wong',A.
    4, A20, 'topic 2',...........'Wong',H.M.
    5, A20, 'topic 2',...........'Wong',T.

    Is there any way to return just one of these rows per Author last Name?
    I know they are different 'Wong's, but the query from the form is for a 'Wong'.

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    You have to drop the "different" wongs and the ID if you want the records to be the same. The entire row is accounted for when using DISTINCT. Since the initials and ID are different, the row is different.

    Think of it from the database's side of things. How do you expect it to figure out that "auth.Initials" should NOT be included in evaluating for DISTINCT, but all of the other columns should? Where did you tell it about that? Which one should it choose?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2011

    3 table join

    Ok, so can you recommend an approach to searching the Abstract table while still using the other 2 in a join?

    So I need to search that table separately?
    Or do some in-memory coding to get the results I want?

Tags for this Thread

Posting Permissions

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