Problem faced in Join(T-SQL)

Table1:

RouteIDRDate Direction RTime Employee_IDArrTime

R16/15/2005Pick6/15/2005 9:30:00 AM14306/15/2005 8:12:00 AM
R26/15/2005Pick6/15/2005 3:00:00 PM31686/15/2005 2:28:00 PM
R26/15/2005Pick6/15/2005 3:00:00 PM14306/15/2005 1:48:00 PM
R26/15/2005Pick6/15/2005 3:00:00 PM19056/15/2005 1:42:00 PM
R36/15/2005Pick6/15/2005 3:00:00 PM35066/15/2005 2:16:00 PM

__________________________________________________ ______________________________________________

Table2:

Employee_ID rDateLoginTimeLogoutTime

34746/15/20056/15/2005 4:45:00 PM6/16/2005 5:45:00 AM
34936/15/20056/15/2005 3:00:00 PM6/16/2005 4:00:00 AM
14306/15/20056/15/2005 9:30:00 AM6/15/2005 2:45:00 PM
18276/15/20056/15/2005 4:45:00 PM6/16/2005 5:45:00 AM
19056/15/20056/15/2005 3:00:00 PM6/16/2005 5:00:00 AM

__________________________________________________ ________________________________________________

I want to select Employee_ID from Table1 who is also found in Table2,
even if there are duplicate records of Employee

I need a single Query(SubQueries) to fetch the above fields

Note: Criteria such as RouteID, RTime and ArrTime should not be taken
into considerations while
framing Queries

I faced problem due to there are duplicate records of EMployees(1430)
__________________________________________________ ________________________________________________
I have tried like this ( as shown below):

SELECT *
FROM Table1
WHERE RDate = cdate('06/15/2005') and Employee_ID
not in (select TAble2.Employee_ID from Schedule Inner Join Table1 on
Table2.LoginTime = Table1.RTime and Table2.Employee_ID =
Table1.Employee_ID);mahesh j k (maheshjk@.gmail.com) writes:
> I want to select Employee_ID from Table1 who is also found in Table2,
> even if there are duplicate records of Employee
> I need a single Query(SubQueries) to fetch the above fields
> Note: Criteria such as RouteID, RTime and ArrTime should not be taken
> into considerations while
> framing Queries
> I faced problem due to there are duplicate records of EMployees(1430)
> ...
> I have tried like this ( as shown below):
> SELECT *
> FROM Table1
> WHERE RDate = cdate('06/15/2005') and Employee_ID
> not in (select TAble2.Employee_ID from Schedule Inner Join Table1 on
> Table2.LoginTime = Table1.RTime and Table2.Employee_ID =
> Table1.Employee_ID);

If memory serves, cdate() is an Oracle function. At least there is no
such function in MS SQL Server, so you may be in the wrong newsgroup.

I cannot really connect your narrative with your attempt to query. Further
more, the query includes a table Schedule which you don't give any more
information. But it could be that Schedule is the real name for Table2.

Here is a very wild guess of what you might be looking for:

SELECT *
FROM Table1 t1
WHERE RDate = cdate('06/15/2005')
and NOT EXISTS (SELECT *
FROM Table2 t2
WHERE t2.LoginTime = t1.RTime
and t2.Employee_ID = t1.Employee_ID);

If this does give you desired result, please follow this standard
recommendation and include:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it very easy to post a tested solution.

Obviously, if you are using Oracle, you should post to an Oracle newsgroup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp