August 19, 2009 at 9:06 pm
Hi,
I have 2 tables:
a) EntryTable
- EntryDateTime
- EmpName
sample records:
1) EntryDateTime: 19-Aug-09 08:50:31
EmpName: Ash
2) EntryDateTime: 19-Aug-09 09:00:00
EmpName: Bob
b) ExitTable
- EntryDateTime
- ExitDateTime
- EmpName
sample records:
1) EntryDateTime: 19-Aug-09 09:00:00
ExitDateTime: 19-Aug-09 12:02:45
EmpName: Bob
I need to create a sql selection when user key in EmpName, it will display out EntryDateTime, ExitDateTime and EmpName.
In the above example, when user key in "Ash", ExitDateTime will be empty.
Please help. Thanks
August 19, 2009 at 9:59 pm
This is just a simple left outer join:
select
entryt.EmpName,
entryt.EntryDateTime,
exitt.ExitDateTime
from
dbo.EntryTable entryt
left outer join dbo.ExitTable exitt
on (entryt.EmpName = exitt.EmpName
and entryt.EntryDateTime = exitt.EntryDateTime)
where
entryt.EmpName = 'Ash';
August 20, 2009 at 12:35 am
Lynn Pettis (8/19/2009)
This is just a simple left outer join:
select
entryt.EmpName,
entryt.EntryDateTime,
exitt.ExitDateTime
from
dbo.EntryTable entryt
left outer join dbo.ExitTable exitt
on (entryt.EmpName = exitt.EmpName
and entryt.EntryDateTime = exitt.EntryDateTime)
where
entryt.EmpName = 'Ash';
thanks for that.
but if I have a case where entryt.EntryDateTime does not match with exitt.EntryDateTime, and I still need to display the record out, how to do that?
August 20, 2009 at 1:29 am
setlan1983 (8/20/2009)
but if I have a case where entryt.EntryDateTime does not match with exitt.EntryDateTime, and I still need to display the record out, how to do that?
Then what is the relationship between these two tables ?
Lynn made the assumption that every row in the entry table would was a corresponding row in the exit table.
Is this just a log people entering a leaving a building ?
What does EntryDateTime mean on the exitt table ?
Are you saying that for each row in the entry table you want to show the first row in the exit table that has an exit time of > entryt.EntryDateTime ?
August 20, 2009 at 2:39 am
Dave Ballantyne (8/20/2009)
setlan1983 (8/20/2009)
but if I have a case where entryt.EntryDateTime does not match with exitt.EntryDateTime, and I still need to display the record out, how to do that?
Then what is the relationship between these two tables ?
Lynn made the assumption that every row in the entry table would was a corresponding row in the exit table.
Is this just a log people entering a leaving a building ?
What does EntryDateTime mean on the exitt table ?
Are you saying that for each row in the entry table you want to show the first row in the exit table that has an exit time of > entryt.EntryDateTime ?
Just in case where someone changed the entryt.EntryDateTime accidently, need show the record in exit table also.
August 20, 2009 at 2:51 am
In that case, you may use full outer join as follows:
select
ISNULL(entryt.EmpName,exitt.EmpName) EmpName,
ISNULL(entryt.EntryDateTime,exitt.EntryDateTime) EntryDateTime,
exitt.ExitDateTime
from
dbo.EntryTable entryt
full outer join dbo.ExitTable exitt
on (entryt.EmpName = exitt.EmpName
and entryt.EntryDateTime = exitt.EntryDateTime)
where
ISNULL(entryt.EmpName,exitt.EmpName) = 'Ash';
August 20, 2009 at 3:10 am
pawan.falor (8/20/2009)
In that case, you may use full outer join as follows:select
ISNULL(entryt.EmpName,exitt.EmpName) EmpName,
ISNULL(entryt.EntryDateTime,exitt.EntryDateTime) EntryDateTime,
exitt.ExitDateTime
from
dbo.EntryTable entryt
full outer join dbo.ExitTable exitt
on (entryt.EmpName = exitt.EmpName
and entryt.EntryDateTime = exitt.EntryDateTime)
where
ISNULL(entryt.EmpName,exitt.EmpName) = 'Ash';
May I know what are the difference between full outer join and left outer join? How is left outer join differ from left join?
thanks
August 20, 2009 at 3:15 am
setlan1983 (8/20/2009)
Just in case where someone changed the entryt.EntryDateTime accidently, need show the record in exit table also.
But WHICH exit row for WHICH entry row ?
Presumably 'Bob' will enter and leave the building many many times and as such will have many entries in both tables. How do expect SQLServer to know which Exit row is related to Which Entry row ?
If someone 'changed' all of Bobs Entry times to '01jan1970' what logic are you using to pick an exit row ?
August 20, 2009 at 4:30 am
Just visit the link below to go thru quick tutorial on sql outer joins
August 20, 2009 at 4:43 am
Dave Ballantyne (8/20/2009)
setlan1983 (8/20/2009)
Just in case where someone changed the entryt.EntryDateTime accidently, need show the record in exit table also.
But WHICH exit row for WHICH entry row ?
Presumably 'Bob' will enter and leave the building many many times and as such will have many entries in both tables. How do expect SQLServer to know which Exit row is related to Which Entry row ?
If someone 'changed' all of Bobs Entry times to '01jan1970' what logic are you using to pick an exit row ?
For such entry, exit time entry will be blank same output as using left outer join query but exit time table itself have entry time hence these rows will be considered even if they don't have matching entry time, due to full outer join.
August 20, 2009 at 6:09 am
setlan1983 (8/20/2009)
Lynn Pettis (8/19/2009)
This is just a simple left outer join:
select
entryt.EmpName,
entryt.EntryDateTime,
exitt.ExitDateTime
from
dbo.EntryTable entryt
left outer join dbo.ExitTable exitt
on (entryt.EmpName = exitt.EmpName
and entryt.EntryDateTime = exitt.EntryDateTime)
where
entryt.EmpName = 'Ash';
thanks for that.
but if I have a case where entryt.EntryDateTime does not match with exitt.EntryDateTime, and I still need to display the record out, how to do that?
I have read the other posts and I have to question the premise.
One, since there are two separate tables, EntryTable and ExitTable, there must be a relationship between the two tables in order to link them together. With the current known information that relationship has to be the EmpName and EntryDateTime. Based on this, that means the EntryDateTime column in the ExitTable cannot be modified directly or you lose that relationship. If there is another unique key in the EntryTable that can uniquely relate a record there to the corresponding record in the ExitTable without using the EntryDateTime then that key should be used instead.
As a general note, however, I would actually merge the two tables together allowing null values in the ExitDateTime and then you don't need to worry about the join between the two tables.
August 23, 2009 at 8:40 pm
Dave Ballantyne (8/20/2009)
setlan1983 (8/20/2009)
Just in case where someone changed the entryt.EntryDateTime accidently, need show the record in exit table also.
But WHICH exit row for WHICH entry row ?
Presumably 'Bob' will enter and leave the building many many times and as such will have many entries in both tables. How do expect SQLServer to know which Exit row is related to Which Entry row ?
If someone 'changed' all of Bobs Entry times to '01jan1970' what logic are you using to pick an exit row ?
Surely beside EmpName, will use ExitDateTime as a selection too.
Example:
where
(entryt.EmpName = 'Ash') AND (exitt.ExitDateTime >= '01-Aug-09')
September 3, 2009 at 8:16 pm
But in this case, I MUST use the EntryDateTime as one of the where selection right? I CANNOT use ExitDateTime as selection because it might return me 0 record?
Am I correct?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply