On Tue, Jul 08, 2008 at 09:09:05AM -0700, Andrea Connell wrote:
> 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');
I think what you want is a join that produces rows with applicantid,
question, and answer columns:
SELECT a.applicantid AS applicantid, q.question AS question,
a.answer AS answer FROM tblanswers a JOIN tblquestions q ON
questionid;
At least that's the right approach if you want to do anything besides
displaying the data. If you just want to display the data as
applicantid, questions, and answers, then you should try the
group_concat() approach others posted about.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
