Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: 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:

  2. #2
    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

  3. #3
    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

  4. #4
    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

  5. #5
    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'

  6. #6
    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.

  7. #7
    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

  8. #8
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •