August 24, 2006 at 6:38 am
Guys hi,
i have an easy question for you. Please respond.
I have a table [DATEBOOK] that registers door entries/exits.
this table has got 2 foreign keys, the sys_nr and the door_nr, and also the fileds datetime , and personname.
for example for the person myself the todays all entries and exits for today are listed below
personname datetime sys_nr door_nr
myself 12.01 1 1
myself 12.10 1 2
myself 12.30 2 1
myself 12.50 2 2
The above tells us that i have entered/exited the building 4 times in total. however in order to identify the door that i used and whether i exited or entered through that door , we have another table called [READERS] that has the readers of the doors. for example
Door Reader sys_nr door_nr
Entry reader 7th flor 1 1
Exit Reader 7th Floor 1 2
Entry Reader 6th Floor 1 3
Exit Reader 6th Floor 1 4
Obviously both the sys_nr and door_nr from the first table, define what exact reader i accessed.
But if i try to inner join the 2 tables like, trying to satisfy both conditions, like:
select....
from DATEBOOK
inner join READERS
-- on (datebook.sys_nr=les.readers_nr and datebook.tuer_nr=readers.tuer_nr)
i get tottaly wrong number of records and double entries
How can i join the two tables? Can you please advice?
August 24, 2006 at 7:00 am
SELECT d.personname, d.[datetime], d.sys_nr, d.door_nr, r.[Door Reader]
FROM [DATEBOOK] d
INNER JOIN [READERS] r
ON r.sys_nr = d.sys_nr
AND r.door_nr = d.door_nr
should give you
personname datetime sys_nr door_nr Door Reader
myself 12.01 1 1 Entry reader 7th flor
myself 12.10 1 2 Exit Reader 7th Floor
in your query you have an alias les, what table it it and you are joining with different column names to the tables, is there a foreign language in use here
Far away is close at hand in the images of elsewhere.
Anon.
August 24, 2006 at 7:05 am
August 24, 2006 at 7:49 am
Is there any duplicate data, ie does your READER table have multiple entries for multiple door entry
Far away is close at hand in the images of elsewhere.
Anon.
August 24, 2006 at 8:14 am
Hi
use Distinct:
SELECT DISTINCT d.personname, d.[datetime], d.sys_nr, d.door_nr, r.[Door Reader]
FROM [DATEBOOK] d
INNER JOIN [READERS] r
ON r.sys_nr = d.sys_nr
AND r.door_nr = d.door_nr
OR
GROUP BY:
SELECT d.personname, d.[datetime], d.sys_nr, d.door_nr, r.[Door Reader]
FROM [DATEBOOK] d
INNER JOIN [READERS] r
ON r.sys_nr = d.sys_nr
AND r.door_nr = d.door_nr GROUP BY d.personname, d.[datetime], d.sys_nr, d.door_nr, r.[Door Reader]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply