Sub Query or Join

[tim44202]

I want to select all timecards for an employee only when the employee has at least one timecard in dept 100. The employee could work in an unlimited number of depts.

 

[checkai]

i would use an inner join...

 

[tim44202]

Since there is only one table I assume it joins to itself? How? Can you give me an example.

 

[checkai]

there is only 1 table? isn't there a staff table and a time table?

 

[donutman]

If not you can use a subquery that filters out employees without at least one department 100 timecard.

   [Blue]SELECT[/Blue] Fields [Blue]FROM[/Blue] TimeCards [Blue]WHERE[/Blue] EEId [Blue]IN[/Blue] [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] EEId [Blue]FROM[/Blue] TimeCards [Blue]WHERE[/Blue] Dept[Gray]=[/Gray]100[Gray])[/Gray]

This can be done other ways but this is probably the easiest way to understand it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[tim44202]

no just a time table.

Example:

Dept Emp# Hours

10 123 12.5
15 222 5.0
20 123 6.5
10 333 14.1
18 444 40.0

If I want all time cards for employees who worked in Dept 10. I want:

10 123 12.5
20 123 6.5