Easy Question please answer

Question

Post reply

Easy Question please answer

Dfalir

SSCrazy

Points: 2486
More actions
August 24, 2006 at 6:38 am

#116071

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?

"If you want to get to the top, prepare to kiss alot of bottom"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

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.

Dfalir SSCrazy Points: 2486 More actions · Answer 2

dear david this is exactly what i do, and i get instead of 5 results, 40 results. (les, is the table readers). have you got any other suggestions? how come and i get all possible results?

"If you want to get to the top, prepare to kiss alot of bottom"

David Burrows SSC Guru Points: 65142 More actions · Answer 3

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.

MexiCano SSC Enthusiast Points: 179 More actions · Answer 4

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]