Can't Use Distinct here!

santal_maluko · #1 (**permalink**) 01-27-05, 09:53

Hi All!!!
I'd Like to know how can I select one only row for each reference... I will post here the SQL clause
Note that I am working on Access

SELECT Badges.BadgeReference, Visitors.VisitorName, EventVisitors.VisitorCheckOutDate, EventVisitors.Event, EventVisitors.VisitorBadge, Visitors.VisitorState
FROM (Visitors INNER JOIN Badges ON Visitors.VisitorReference = Badges.BadgeReference) INNER JOIN EventVisitors ON (Visitors.Visitor_ID = EventVisitors.Visitor) AND (Badges.Badge_ID = EventVisitors.VisitorBadge)
WHERE (((Badges.BadgeReference) Is Not Null) AND ((EventVisitors.VisitorCheckOutDate) Is Not Null) AND ((Visitors.VisitorState)=0))
ORDER BY EventVisitors.VisitorCheckOutDate DESC;

It returns to me all the time the badge was out in each event... I want it to show the last event that each BadgeReference was retrieved...

Thanks in advance!

r937 · #2 (**permalink**) 01-27-05, 11:47

Code:

SELECT Badges.BadgeReference
     , Visitors.VisitorName
     , EV.VisitorCheckOutDate
     , EV.Event
     , EV.VisitorBadge
     , Visitors.VisitorState
  FROM (
       Visitors 
INNER 
  JOIN Badges 
    ON Visitors.VisitorReference 
     = Badges.BadgeReference
       ) 
INNER 
  JOIN EventVisitors as EV
    ON Visitors.Visitor_ID = EV.Visitor
   AND Badges.Badge_ID = EV.VisitorBadge
 WHERE Badges.BadgeReference Is Not Null
   AND EV.VisitorCheckOutDate 
     = ( select max(VisitorCheckOutDate)
           from EventVisitors
          where VisitorBadge = EV.VisitorBadge )
   AND Visitors.VisitorState = 0
ORDER 
    BY EV.VisitorCheckOutDate DESC

santal_maluko · #3 (**permalink**) 01-27-05, 12:00

Thank you very very very very and a lots of many manys Much!!!!!

You have saved my day.... You sure are pretty good at this

See ya

Keep up that good work that you do

Internet Services

Web Development

Digital Marketing

Consumer Tech