-
Help with Distinct function.
I have a query that returns employee names and reservations they updated for a set date. Here's a simplified version:
Select Employee.Name, Reservation.ResID
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where Reservation.DateModified BETWEEN '200510010000' AND '200510050000'
The problem is each employee can update the same reservation several times but I only want it to display that record once. The distinct statement doesn't work because I want the EmpID to be listed more than once. Thanks.
-
just add another column to your select statement Reservation.DateModified
-
If the reservation is modified twice within that timeframe, it will show up twice in the result. I need it to only show up once. Thanks.
-
How about using DISTINCT
Select Distinct Employee.Name, Reservation.ResID
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where Reservation.DateModified BETWEEN '200510010000' AND '200510050000'
-
Questions: What do you want the outcome to be?
Do you want 1 record for an employee but you don't know if there are multiple reservations and you only want to know if he has a reservation or not between a given time frame?
Do you Want a record for ever time an employee makes a reservation change between a given time frame with the posibility of multiple reservations?
Do you want a record for each change to a reservation between a given time frame for a given employee?
Does the reservation table have multiple records for an employee?
Does the employee table have multiple records for an employee for some reason?
-
Originally Posted by Frank Johnson
Questions: What do you want the outcome to be?
Do you want 1 record for an employee but you don't know if there are multiple reservations and you only want to know if he has a reservation or not between a given time frame?
Do you Want a record for ever time an employee makes a reservation change between a given time frame with the posibility of multiple reservations?
Do you want a record for each change to a reservation between a given time frame for a given employee?
Does the reservation table have multiple records for an employee?
Does the employee table have multiple records for an employee for some reason?
I want the outcome to list only the first modification of the reservation and by what employee. So the query will list the employee several times and what reservation he/she updated initially. For example, say John updated 5 reservations, two times each, for a total of 10 records. I would like the query to show 5 records of John and the initial update of each reservations.
Yes, the reservation table has an entry (by ResID) for each update. In the above case, there would be a record inserted for each time a reservation was modified.
The employee table has the employee listed only once.
-
>>
The problem is each employee can update the same reservation several times but I only want it to display that record once. The distinct statement doesn't work because I want the EmpID to be listed more than once. Thanks
[..]
I want the outcome to list only the first modification of the reservation and by what employee. So the query will list the employee several times and what reservation he/she updated initially.
<<
Like other people here, I think the DISTINCT keyword *should* work because the DISTINCT would apply to the combination of employee ID + reservation ID, so you *should* get the employee appearing more than once. But it might not be optimal for making sure that only the *first* mod is shown...
... Would something like this do what you're looking for (I may be misunderstanding)?
Code:
Select Employee.Name, Reservation.ResID,
MIN(Reservation.DateModified) AS FirstMod
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where [..]
Group By Employee.Name, Reservation.ResID
>L<
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|