I have 3 tables:

session
SessID PK

Author
AbstractNo PK
SessID FK


Abstract
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'.

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?

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?