| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-12-03, 16:36
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 4
|
|
|
2 LEFT JOINS ? _-- survey reporting question
|
|
I'm trying to exlude from a survey report participants who have not finished the survey -- I want only participants who have completed the survey as represented by a date entry in _participant_log.date_finish. _
Here's a typical result, all participants including those who didn't finish survey (more detail to follow):
question 321:
----------------------------
Practitioner _ _ _ _ | _88
Academic _ _ _ _ _ _ | 8
Consulting Services _| _38
Vendor _ _ _ _ _ _ _ | _12
Student _ _ _ _ _ _ | _ 1
Other _ _ _ _ _ _ _ _| _34
----------------------------
The problem: unless a choice has been chosen by at least one participant who has finished, it won't be included in the results _if_ I limit the report to those who have finished survey.
Here the 'Student' choice is not included because the one person who selected it did not complete survey
question 321:
----------------------------
Practitioner _ _ _ _ | _88
Academic _ _ _ _ _ _ | _ 8
Consulting Services _| 38
Vendor _ _ _ _ _ _ _ | 12
Other _ _ _ _ _ _ _ _| 34
----------------------------
Here are the tables and relationships:
question => question_choice -> response_choice
participant_log (
CREATE TABLE question (
_ id int(10) unsigned NOT NULL auto_increment,
_ survey_id int(10) unsigned NOT NULL default '0',
_ question text NOT NULL,
_ sequence int(10) unsigned NOT NULL default '0',
_ PRIMARY KEY _(id),
_ KEY survey_id (survey_id),
) TYPE=MyISAM;
_ _
CREATE TABLE question_choice (
_ id int(10) unsigned NOT NULL auto_increment,
_ question_id int(10) unsigned NOT NULL default '0',
_ choice varchar(100) NOT NULL default '',
_ sequence tinyint(4) NOT NULL default '0',
_ PRIMARY KEY _(id),
_ KEY question_id (question_id),
) TYPE=MyISAM;
_ _
CREATE TABLE response_choice (
_ participant_id int(10) unsigned NOT NULL default '0',
_ choice_id int(10) unsigned NOT NULL default '0',
_ PRIMARY KEY _(choice_id,participant_id),
_ KEY participant_id (participant_id)
) TYPE=MyISAM ;
_ _
CREATE TABLE participant_log (
_ participant_id int(10) unsigned NOT NULL default '0',
_ survey_id int(10) unsigned NOT NULL default '0',
_ date_start datetime default NULL,
_ date_finish datetime default NULL,
_ PRIMARY KEY _(participant_id,survey_id),
) TYPE=MyISAM CHAR
Basic question report SQL:
SELECT _qc.choice, COUNT(r.participant_id) AS count
_ _ FROM question_choice qc,
_ _ question q LEFT JOIN response_choice r ON (r.choice_id =qc.id)
_ _ WHERE question_id =321 AND qc.question_id =q.id
_ _ GROUP BY qc.id
_ _
Result:
----------------------------
Practitioner _ _ _ _ | _88
Academic _ _ _ _ _ _ | _ 8
Consulting Services _| _38
Vendor _ _ _ _ _ _ _ | _12
Student _ _ _ _ _ _ _| _ 1
Other _ _ _ _ _ _ _ _| _34
----------------------------
The Probem SQL:
This SQL limits the report to only those participants who have finished the survey. _Unless a choice has been chosen by at least one participant who has finished, it won't be included in the results.
SELECT _qc.choice, COUNT(r.participant_id) AS count
_ _FROM participant_log pl, question_choice qc,
_ _question q LEFT JOIN response_choice r ON (r.choice_id =qc.id)
_ _WHERE pl.date_finish IS _NOT _NULL AND question_id =321
_ _AND r.participant_id = pl.participant_id AND pl.survey_id = 2 AND qc.question_id =q.id
_ _GROUP BY qc.id
Note: the ' AND pl.survey_id = 2 ' is necessary because the participant may have other records in participant_log from other surveys. The participant_log key is participany_id+survey_id. I can also get the same effect by using _'AND q.survey_id=pl.survey_id' instead:
|
|
12-12-03, 21:50
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 91
|
|
|
Re: 2 LEFT JOINS ? _-- survey reporting question
would this work?
SELECT qc.choice, COUNT(r.participant_id) AS count
FROM participant_log pl,
response_choice r,
question q,
question_choice qc
WHERE r.participant_id = pl.participant_id
AND r.choice_id = qc.id
AND qc.question_id = q.id
AND pl.date_finish IS NOT NULL
AND pl.survey_id = 2
AND q.id = 321
GROUP BY qc.choice
UNION
SELECT qc.choice, 0 AS count
FROM participant_log pl,
question q,
question_choice qc LEFT JOIN response_choice r ON (r.choice_id = qc.id)
WHERE r.participant_id = pl.participant_id
AND qc.question_id = q.id
AND pl.participant_id IS NULL
AND q.survey_id = 2
AND q.id = 321
|
|
12-12-03, 23:51
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 4
|
|
|
|
still leaving out out the choices not chosen by at least one participant who has finished. seems like I need to add another LEFT JOIN but I'm not sure how to do it
|
|
12-13-03, 12:16
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 91
|
|
SELECT qc.choice, SUM(IF(pl.date_finish is null,0,1)) AS count
FROM participant_log pl,
question q,
question_choice qc,
response_choice r
WHERE r.participant_id = pl.participant_id
AND r.choice_id = qc.id
AND qc.question_id = q.id
AND pl.survey_id = 2
AND q.id = 321
GROUP BY qc.choice
UNION
SELECT qc.choice, 0 AS count
FROM question q,
question_choice qc LEFT JOIN response_choice r ON (r.choice_id = qc.id)
WHERE qc.question_id = q.id
AND r.choice_id IS NULL
AND q.survey_id = 2
AND q.id = 321
|
|
12-14-03, 19:05
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 4
|
|
I have super-simplified this.... _ Cut and paste the SQL and see what happens.
Code:
CREATE TABLE question_choice (
id int(11) NOT NULL auto_increment,
question_id int(11) NOT NULL default '0',
choice varchar(100) character set latin1 NOT NULL default '',
PRIMARY KEY (id),
KEY question_id (question_id),
) TYPE=MyISAM ;
CREATE TABLE response_choice (
participant_id int(10) unsigned NOT NULL default '0',
choice_id int(10) unsigned NOT NULL default '0',
PRIMARY KEY (participant_id,choice_id),
KEY choice_id (choice_id)
) TYPE=MyISAM;
CREATE TABLE participant_log (
participant_id int(11) NOT NULL default '0',
survey_id int(11) NOT NULL default '0',
date_start datetime default NULL,
date_finish datetime default NULL,
PRIMARY KEY (participant_id,survey_id)
) TYPE=MyISAM;
# id,question_id,choice:
INSERT INTO question_choice VALUES (13, 4, 'Head-aches');
INSERT INTO question_choice VALUES (14, 4, 'Dizzines');
INSERT INTO question_choice VALUES (15, 4, 'Hallucinations');
INSERT INTO question_choice VALUES (16, 4, 'Other');
# participant 101:
INSERT INTO response_choice VALUES (101, 13);
INSERT INTO response_choice VALUES (101, 14);
# participant 102:
INSERT INTO response_choice VALUES (102, 13);
INSERT INTO response_choice VALUES (102, 14);
INSERT INTO response_choice VALUES (102, 15);
INSERT INTO participant_log VALUES (101, 1, '2003-12-14 13:04:10', '2003-12-14 13:04:26');
INSERT INTO participant_log VALUES (102, 1, '2003-12-14 13:04:38', NULL);
Basic Summary SQL:
Code:
SELECT qc.choice, COUNT(participant_id) AS count
FROM question_choice qc
LEFT JOIN response_choice r ON (r.choice_id =qc.id)
WHERE question_id =4
GROUP BY qc.id
Choice: Count:
-------------------
Head-aches 2
Dizzines 2
Hallucinations 1
Other 0
Now we want to limit results to those participants who have finished survey
as represented by a non-NULL value in participant_log.date_finish
Code:
SELECT qc.choice, COUNT(r.participant_id) AS count
FROM question_choice qc, participant_log pl
LEFT JOIN response_choice r ON (r.choice_id =qc.id)
WHERE pl.date_finish IS NOT NULL AND pl.survey_id = 1 AND question_id =4
AND r.participant_id = pl.participant_id
GROUP BY qc.id
Choice: Count:
-------------------
Head-aches 1
Dizzines 1
Correct but missing the Zero count totals for 'Hallucinations' and 'Other'
|
|
12-14-03, 19:19
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 91
|
|
So what was wrong with the SQL I posted yesterday at 16:16?
Of course you have to delete
AND pl.survey_id = 2
AND q.id = 321
to make the query more general purpose, but other than that...
it ran on my computer just fine.
|
|
12-14-03, 22:21
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 4
|
|
ALmost perfect, but it leaves out the choice 'Other', I think because it was not selected by anyone, whereas 'Hallucinations' was chosen, but not by a participant who completed the survey
Dizzines 1
Head-aches 1
Hallucinations 0
|
|
12-14-03, 23:34
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 91
|
|
mysql> SELECT qc.choice, SUM(IF(pl.date_finish is null,0,1)) AS count
-> FROM participant_log pl,
-> question q,
-> question_choice qc,
-> response_choice r
-> WHERE r.participant_id = pl.participant_id
-> AND r.choice_id = qc.id
-> AND qc.question_id = q.id
-> AND q.survey_id = 2
-> AND q.id = 4
-> GROUP BY qc.choice
-> UNION
-> SELECT qc.choice, 0 AS count
-> FROM question q,
-> question_choice qc LEFT JOIN response_choice r ON (r.choice_id = qc.id)
-> WHERE qc.question_id = q.id
-> AND r.choice_id IS NULL
-> AND q.survey_id = 2
-> AND q.id = 4;
+----------------+-------+
| choice | count |
+----------------+-------+
| Dizzines | 1 |
| Hallucinations | 0 |
| Head-aches | 1 |
| Other | 0 |
+----------------+-------+
4 rows in set (0.00 sec)
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
2 LEFT JOINS ? _-- survey reporting question
