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
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?