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:

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!What table is TangoID in?

blindman|||Originally posted by blindman
What table is TangoID in?

blindman

tblTrip|||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:

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.|||Originally posted by snail
Use group by and aggregate functions.

You uh, mind being a little more specific?|||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