Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    23

    Exclamation Unanswered: Can't Use Distinct here!

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    23
    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

Posting Permissions

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