I have a Firebird Db about RFID card readers. I am getting data from the RFID card readers to the Firebird DB. There is 4 tables in my sql query;

P is Personnel table (Name, ID etc.)
C is Card Readers table (Name, ID etc.)
G is Starting date/time table (Date & time about when personnel start to work)
G2 is Ending date/time table (Date & time about when personnel finish to work)


Everyday our personnels use RFID cards to begin and quit work.

Now this is my first SQL about getting records:

First SQL:
SELECT P.P_ADI AS AD,P.S_NO AS SICIL,C.CIHAZADI AS CIHAZ,
G.TARIH AS ISLEMTARIH,G.SAAT AS GIRIS,
g2.tarih as ISLEMTARIH2,G2.SAAT AS CIKIS
FROM PERSONEL P,CIHAZ C,GIRISCIKIS G,GIRISCIKIS2 G2
WHERE P.P_KODU=184
AND G.PKODU=P.P_KODU
AND G2.PKODU=P.P_KODU
AND G.CNO=C.CIHAZNO
AND (G.TARIH>='31.05.2013'
AND G.TARIH<='03.06.2013'
AND G.SAAT>='00:00:00' AND G.SAAT<='23:59:59'
AND G2.TARIH>='31.05.2013'
AND G2.TARIH<='03.06.2013')
AND G2.SAAT>='00:00:00'
AND G2.SAAT<='23:59:59'
AND G.ISLEM=0
AND G2.ISLEM=1
ORDER BY AD, ISLEMTARIH, GIRIS, CIKIS ASC

I wrote information about table names in up. Lets write the others, because it is in Turkish.

P.P_ADI = name of personnel
P.S_NO = personnel special number
C.CIHAZADI = RFID name
G.TARIH = Start date
G.SAAT = Start time
G2.TARIH = Quit Date
G2.SAAT = Quit time

Result of first SQL Query:

Name SNumber RFID SDate STime QDate QTime
--------------------------------------------------------------
X Person X Numb XRFID 31/05/13 08:19 31/05/13 16:49
X Person X Numb XRFID 31/05/13 08:19 01/06/13 18:05
X Person X Numb XRFID 03/06/13 08:14 31/05/13 16:49
X Person X Numb XRFID 03/06/13 08:14 01/06/13 18:05


But there are some mistakes in this query. The values which i made them bold, are wrong.

I want in the same row if there are start and quit entries for person, also i want if there is only start or quit entry. I mean maybe personnel can started to work with RFID but left without RFID. Or start without RFID and quit with RFID. I want to see all. But i want to see null if there is not entry about start or quit.

In this query i got mixed records.

I tried another query too:

LAST SQL:
SELECT P.P_ADI AS AD, P.S_NO AS SICIL, C.CIHAZADI AS CIHAZ,
G.TARIH AS ISLEMTARIH, G.SAAT AS GIRIS,
g2.tarih as ISLEMTARIH2,G2.SAAT AS CIKIS
FROM PERSONEL P,CIHAZ C,GIRISCIKIS G,GIRISCIKIS2 G2
WHERE P.P_KODU=184
AND G.PKODU=P.P_KODU
AND G2.PKODU=P.P_KODU
AND G.CNO=C.CIHAZNO
AND G.TARIH>='31.05.2013'
AND G.TARIH<='03.06.2013'
AND G.SAAT>='00:00:00' AND G.SAAT<='23:59:59'
AND G2.TARIH=G.TARIH
AND G2.SAAT>='00:00:00'
AND G2.SAAT<='23:59:59'
AND G.ISLEM=0
AND G2.ISLEM=1
ORDER BY AD, ISLEMTARIH, GIRIS, CIKIS ASC

RESULT of last SQL Query:
Name SNumber RFID SDate STime QDate QTime
--------------------------------------------------------------
X Person X Numb XRFID 31/05/13 08:19 31/05/13 16:49

I got data only if personnel start and quit with RFID. But another records are not showing.


And this the result what i want:

Name SNumber RFID SDate STime QDate QTime
--------------------------------------------------------------
X Person X Numb XRFID 31/05/13 08:19 31/05/13 16:49
X Person X Numb XRFID No record START 01/06/13 18:05
X Person X Numb XRFID 03/06/13 08:14 No record QUIT

I mean records which personnel start and finish work with RFID or start only with RFID or finish only RFID

How can i make this with SQL?

Thank for help.