my approah would be a table for applicants, then the table for answers would
have the applicantid and the questionid as well as the answer.
CREATE TABLE tblApplicants (applicantid int, applicantname varchar(100));
now just do a select joining the tables
select ap.applicantname, qu.question, an.answer
from tblanswers an
inner join tblApplicants ap
on ap.applicantid = an.applicantid
inner join tblquestions qu
on qu.questionid = an.questionid
-- optional where clause to select just 1 applicant
where ap.applicantid = 1
-- optional order by clause to make it neat
order by applicantname asc, qu.questionid asc
--- On Tue, 7/8/08, Andrea Connell <[EMAIL PROTECTED]> wrote:
From: Andrea Connell <[EMAIL PROTECTED]>
Subject: [sqlite] View with Dynamic Fields ?
To: sqlite-users@sqlite.org
Date: Tuesday, July 8, 2008, 12:09 PM
I'm not sure if what I want is possible to do in SQL, but I -am- sure
that one of you will know.
Given two tables - one with questions to ask applicants and one with an
applicant's answer to a particular question - I want to make a flattened
view with all of an applicant's answers in one row. This is easy enough
to do when I know ahead of time which questions are in the first table,
but I don't like the hard-coded approach.
My schema is as follows:
CREATE TABLE tblquestions (questionid int, question varchar(100));
INSERT INTO "tblquestions" VALUES(1,'whats up');
INSERT INTO "tblquestions" VALUES(2,'how are you');
INSERT INTO "tblquestions" VALUES(3,'whats your name');
CREATE TABLE tblanswers (questionid int, applicantid int, answer
varchar(2500));
INSERT INTO "tblanswers" VALUES(1,100,'stuff for answer
one');
INSERT INTO "tblanswers" VALUES(2,100,'stuff for answer
two');
INSERT INTO "tblanswers" VALUES(3,100,'stuff for answer
three');
INSERT INTO "tblanswers" VALUES(1,200,'random text one');
INSERT INTO "tblanswers" VALUES(2,200,'random text two');
INSERT INTO "tblanswers" VALUES(3,200,'random text three');
Here is the view I have come up with so far, which would require editing
whenever an insert or delete is done on tblQuestions.
CREATE VIEW allanswers as
SELECT applicantid,
(select answer from tblanswers Z where questionid = 1 and
Z.applicantid = A.applicantid) As Answer1,
(select answer from tblanswers Z where questionid = 2 and
Z.applicantid = A.applicantid) As Answer2,
(select answer from tblanswers Z where questionid = 3 and
Z.applicantid = A.applicantid) As Answer3
FROM tblanswers A
group by applicantid;
sqlite> select * from allanswers;
100|stuff for answer one|stuff for answer two|stuff for answer three
200|random text one|random text two|random text three
Has anybody come across a problem like this and found a reasonable
dynamic solution? Even something like a trigger on tblQuestions to
change the View would be great, I'm just not sure about the SQL
involved. If it helps, I don't really care what the columns end up being
named. Also I can't guarantee that the questionids will be consecutive
or in any order.
Thanks,
Andrea
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
