posted 12 years ago
Ok the schema goes like this, just now created and tested, worked fine.
CREATE TABLE person(pid BIGINT PRIMARY KEY);
CREATE TABLE students(sid BIGINT PRIMARY KEY,sname VARCHAR(32) NOT NULL, FOREIGN KEY(sid) REFERENCES person(pid));
CREATE TABLE staff(sid BIGINT PRIMARY KEY,sname VARCHAR(32) NOT NULL, FOREIGN KEY(sid) REFERENCES person(pid));
CREATE TABLE attendance(transid BIGINT PRIMARY KEY,pid BIGINT NOT NULL, timein TIMESTAMP NOT NULL, FOREIGN KEY(pid) REFERENCES person(pid));
Selection
======
SELECT s.sname FROM students s, attendance a, person p WHERE date(a.timein)=CURRENT_TIMESTAMP and a.pid=p.pid AND p.pid=s.sid;
Hope I am doing the right thing. Thank you Paul for your replies.
Love all, trust a few, do wrong to none.