Compare with Date and ID problem

Question

Table1

ID Date Intime Outtime

A001   20000501 12:00:00 22:00:00
A001   20000502 14:00:00 22:00:00
A001   20000503 12:00:00 23:00:00
A002   20000501 11:00:00 20:00:00
A002   20000502 13:00:00 21:00:00

So on…,

Table2

ID Date Intime Outtime

A001 20050501 14:00:00 23:00:00
A002 20050501 08:00:00 16:00:00

From the above table

I want to take Table1.ID, Table1.Date, Table2.Intime, Table2.Outtime from Table1 Inner Join Table2 on Table1.ID = Table2.ID and Table1.Date = Table2.Date

Getting Duplicated values

ID Date Intime Outtime

A001   20000501 14:00:00 23:00:00
A001   20000501 18:00:00 16:00:00
A002   20000501 14:00:00 23:00:00
A002   20000501 18:00:00 16:00:00

I tried Left outer Join also. It was showing a same. How to compare the id and date.

Need query Help?

Answer 1

If you do an inner join, you'll only get those rows that are present in both tables (in terms of their ID and Date):

SELECT 
  Table1.ID, Table1.Date, 
  Table2.Intime, Table2.Outtime 
FROM Table1 
INNER JOIN Table2 ON Table1.ID = Table2.ID AND Table1.Date = Table2.Date

ID     Date       InTime   OutTime
A001   20000501   14:00:00 23:00:00
A002   20050501   08:00:00 16:00:00

If you're not getting this, then there's a problem in your data - as I've already mentioned in a previous answer to a previous question.

Check the output of this query:

SELECT * FROM Table2 WHERE ID = 'A001' AND Date = '20000501'

I bet you get more than one row.....

Marc

Trying to explain further - I still think you're misunderstanding the INNER JOIN or you're trying to accomplish something that can't really be done easily.

Your output is something like this:

ID Date Intime Outtime

A001   20000501 14:00:00 23:00:00
A001   20000501 18:00:00 16:00:00
A002   20000501 14:00:00 23:00:00
A002   20000501 18:00:00 16:00:00

If you truly get this output from your INNER JOIN, then this means:

• you most like have one row with ID=A001 and Date=20000501 in your Table1
• you have two (or more) rows in your Table2 with ID=A001 and Date=20000501

What the INNER JOIN will do is combine row#1 from Table2 with the single row from Table1, and then row#2 from Table2 with the single row from Table1, and so on.

If you have multiple entries with the same (ID,Date) values in Table2, you will get duplicates with an INNER JOIN - this is by design, and is not an error - but just the way the INNER JOIN works.