Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Question Unanswered: Pulling a unique field w/ join

    Hi,

    I'm trying to pull some data from two tables, and I need the data where a particular field is unique. Here's what I have right now:
    Code:
    SELECT tblTrip.*, tblTDYTravel.* FROM tblTrip
    INNER JOIN tblTDYTravel ON tblTrip.TripID = tblTDYTravel.TripID
    WHERE tblTrip.EmployeeID = @EmployeeID
    ORDER BY TangoID ASC
    I need to get only the entries where the TangoID field is unique. Any ideas?

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What table is TangoID in?

    blindman

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Originally posted by blindman
    What table is TangoID in?

    blindman
    tblTrip

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Pulling a unique field w/ join

    Originally posted by Tarkon
    Hi,

    I'm trying to pull some data from two tables, and I need the data where a particular field is unique. Here's what I have right now:
    Code:
    SELECT tblTrip.*, tblTDYTravel.* FROM tblTrip
    INNER JOIN tblTDYTravel ON tblTrip.TripID = tblTDYTravel.TripID
    WHERE tblTrip.EmployeeID = @EmployeeID
    ORDER BY TangoID ASC
    I need to get only the entries where the TangoID field is unique. Any ideas?

    Thanks!
    Use group by and aggregate functions.

  5. #5
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Re: Pulling a unique field w/ join

    Originally posted by snail
    Use group by and aggregate functions.
    You uh, mind being a little more specific?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This should do what you SAID you want (only unique tangoIDs):

    SELECT tblTrip.*, tblTDYTravel.*
    FROM tblTrip
    INNER JOIN tblTDYTravel ON tblTrip.TripID = tblTDYTravel.TripID
    INNER JOIN (select TangoID From TBLTrip group by TangoID Having count(*) = 1) UniqueTangoIDs
    on tblTrip.TangoID = UniqueTangoIDs.TangoID
    WHERE tblTrip.EmployeeID = @EmployeeID
    ORDER BY tblTrip.TangoID ASC


    ..whether this is what you MEAN you want is another story.

    blindman

Posting Permissions

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