By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 1,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Problem in Join(T-SQL)

P: n/a
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

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);

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
What you're asking for is quite different from what's in your sample query.
Simply returning a unique list of employees in both Table 1 and Table 2 can
be done like this:

SELECT DISTINCT Table1.EmployeeID FROM Table1 INNER JOIN Table2 ON
Table1.EmployeeID = Table2.EmployeeID

However, your sample suggests that there's more you want to accomplish here.
Could you be more specific?
"mahesh j k" <ma******@gmail.com> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.com...
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

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);

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.