SEO

Table1:

RouteID RDate Direction RTime Employee_ID ArrTime

R1 6/15/2005 Pick 6/15/2005 9:30:00 AM 1430 6/15/2005 8:12:00 AM
R2 6/15/2005 Pick 6/15/2005 3:00:00 PM 3168 6/15/2005 2:28:00 PM
R2 6/15/2005 Pick 6/15/2005 3:00:00 PM 1430 6/15/2005 1:48:00 PM
R2 6/15/2005 Pick 6/15/2005 3:00:00 PM 1905 6/15/2005 1:42:00 PM
R3 6/15/2005 Pick 6/15/2005 3:00:00 PM 3506 6/15/2005 2:16:00 PM

__________________________________________________ ______________________________________________

Table2:

Employee_ID rDate LoginTime LogoutTime

3474 6/15/2005 6/15/2005 4:45:00 PM 6/16/2005 5:45:00 AM
3493 6/15/2005 6/15/2005 3:00:00 PM 6/16/2005 4:00:00 AM
1430 6/15/2005 6/15/2005 9:30:00 AM 6/15/2005 2:45:00 PM
1827 6/15/2005 6/15/2005 4:45:00 PM 6/16/2005 5:45:00 AM
1905 6/15/2005 6/15/2005 3:00:00 PM 6/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/techinf...2000/books.asp