SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    nowhere
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    only true without false - table

    Is it possible to display only this exam which include correct answers for all questions? One exam include 5 question and 5 questions must be correct. If any 'exam id' column include wrong answer then must be rejected.

    I give as an example some of my tables:

    select * from exam;
    +----+----------+---------+---------------------+
    | id | epoch_id | user_id | data |
    +----+----------+---------+---------------------+
    | 1 | 2 | 42 | 2009-10-19 |
    | 2 | 2 | 42 | 2009-10-19 |
    | 32 | 2 | 57 | 2009-10-22 |
    +----+----------+---------+---------------------+

    mysql> select * from user;
    +----+----------+-------------------------------------------+---------------------+
    | id | nick | password | data |
    +----+----------+-------------------------------------------+---------------------+
    | 1 | girl | *8E9D6C8D53D | 2009-10-18 |
    | 52 | boy | *29D53D98F2 | 2009-10-20 |
    | 53 | men | *1C9A6D0E04 | 2009-10-20 |
    | 54 | women | *D9FBF8CFF | 2009-10-20 |
    | 55 | toto | *19584DAD40 | 2009-10-20 |
    | 56 | water | *24830F9243 | 2009-10-20 |
    | 57 | bear | *211A5945F4 | 2009-10-20 |
    +----+----------+-------------------------------------------+---------------------+


    mysql> select * from answer_user;
    +-----+---------+-------------+-------------+---------------------+
    | id | exam_id | question_id | user_answer | data |
    +-----+---------+-------------+-------------+---------------------+
    | 1 | 1 | 27 | b | 2009-10-19 |
    | 2 | 1 | 19 | c | 2009-10-19 |
    | 3 | 8 | 27 | b | 2009-10-19 |
    | 4 | 8 | 19 | c | 2009-10-19 |
    | 158 | 32 | 24 | d | 2009-10-22 |
    +-----+---------+-------------+-------------+---------------------+


    mysql> select * from questions where epoch_id=1;
    +----+----------+-------------+---+---+---+---+-------------+
    | id | epoch_id | question | a | b | c | d | true_answer |
    +----+----------+-------------+---+---+---+---+-------------+
    | 1 | 1 | Where is a? | b | a | c | d | b |
    | 2 | 1 | Where is b? | a | c | b | d | c |
    | 3 | 1 | Where is c? | d | b | a | c | d |
    | 4 | 1 | Where is d? | d | b | a | c | a |
    | 5 | 1 | Where is e? | a | e | c | d | b |
    +----+----------+-------------+---+---+---+---+-------------+



    I want gain something like this:
    +------------+---------+-------------+
    | nick | exam id | check |
    +------------+---------+-------------+
    | toto | 24 | good answer |
    | toto | 24 | good answer |
    | toto | 24 | good answer |
    | toto | 24 | good answer |
    | toto | 24 | good answer |
    | bear | 29 | good answer |
    | bear | 29 | good answer |
    | bear | 29 | good answer |
    | bear | 29 | good answer |
    | bear | 29 | good answer |
    | water | 31 | good answer |
    | water | 31 | good answer |
    | water | 31 | good answer |
    | water | 31 | good answer |
    | water | 31 | good answer |
    | girl | 32 | good answer |
    | girl | 32 | good answer |
    | girl | 32 | good answer |
    | girl | 32 | good answer |
    | girl | 32 | good answer |
    +------------+---------+-------------+

    Thanks in advance for any helps.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    nowhere
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Is it possible to display only this exam which include correct answers for all questions? One exam include 5 question and 5 questions must be correct.


    select * from exam where id=18 or id=22;

    Code:
    id | epoch_id | user_id | date |
    18 | 2_______ | 29_____ | 2009-10-22 15:57:17 |
    22 | 2_______ | 32_____ | 2009-10-19 20:26:49 |
    select * from user where id=29 or id=32;

    Code:
    id | nick | password_________________________________ | date |
    29 | bear | *29D53D24BBD1990B38642F839CD33910B85958F2 | 2009-10-20 14:15:31 |
    32 | girl | *8E9D6C8782526823B033261F3F6F1028E2D6D53D | 2009-10-18 21:55:19 |


    select * from answer_user where exam_id=18 or exam_id=22;

    Code:
    id_ | exam_id | question_id | user_answer | date |
    142 | 18_____ | 26_________ | c__________ | 2009-10-19 22:15:17 |
    142 | 18_____ | 23_________ | c__________ | 2009-10-19 22:15:17 |
    142 | 18_____ | 24_________ | d__________ | 2009-10-19 22:15:17 |
    142 | 18_____ | 22_________ | a__________ | 2009-10-19 22:15:17 |
    142 | 18_____ | 25_________ | a__________ | 2009-10-19 22:15:17 |
    
    153 | 22_____ | 23_________ | c__________ | 2009-10-22 15:57:27 |
    153 | 22_____ | 25_________ | a__________ | 2009-10-22 15:57:27 |
    153 | 22_____ | 27_________ | c__________ | 2009-10-22 15:57:27 |
    153 | 22_____ | 21_________ | b__________ | 2009-10-22 15:57:27 |
    153 | 22_____ | 17_________ | b__________ | 2009-10-22 15:57:27 |

    select * from questions where id=26 or id=23 or id=24 or id=22 or id=25;

    Code:
    id | epoch_id | question___| a | b | c | d | true_answer |
    26 |________2 |Where is d? | b | a | d | c | c |
    23 |________2 |Where is a? | d | c | a | b | c |
    24 |________2 |Where is c? | a | d | b | c | d |
    22 |________2 |Where is e? | e | c | d | a | a |
    25 |________2 |Where is b? | b | a | d | c | a |
    select * from questions where id=23 or id=25 or id=27 or id=21 or id=17;

    Code:
    23 |________2 |Where is a? | d | c | a | b | c |
    25 |________2 |Where is b? | b | a | d | c | a |
    27 |________2 |Where is f? | b | d | f | c | c |
    21 |________2 |Where is g? | b | g | d | c | b |
    17 |________2 |Where is h? | b | h | d | c | b |

    If any exam_id column in exam table include wrong answer then must be rejected. Happily, users bear and girl answered for all questions good. I want gain something like this without wrong answers or answer:

    Code:
    nick | exam id | check |
    bear | 29_____ | good answer | 
    bear | 29_____ | good answer | 
    bear | 29_____ | good answer | 
    bear | 29_____ | good answer | 
    bear | 29_____ | good answer | 
    girl | 32_____ | good answer | 
    girl | 32_____ | good answer | 
    girl | 32_____ | good answer | 
    girl | 32_____ | good answer | 
    girl | 32_____ | good answer |

    I couldn't better explain my question.

    Thanks in advance for any helps.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    nowhere
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I write this query:

    select user.nick, exam.id as 'exam id', (CASE WHEN answer_user.user_answer = questions.true_answer THEN 'good answer' ELSE 'bad answer' END) AS check
    from user join exam, answer_user, questions
    where user.id=exam.user_id
    and answer_user.exam_id=exam.id
    and answer_user.question_id=questions.id
    order by exam.id;

    then it return me this result:

    Code:
    +------------+---------+--------+
    | nick       | exam id | spr    |
    +------------+---------+--------+
    | mis        |      31 | dobrze | 
    | mis        |      31 | dobrze | 
    | mis        |      31 | dobrze | 
    | mis        |      31 | dobrze | 
    | mis        |      31 | dobrze | 
    | mis        |      32 | dobrze | 
    | mis        |      32 | źle    | 
    | mis        |      32 | dobrze | 
    | mis        |      32 | źle    | 
    | mis        |      32 | źle    | 
    +------------+---------+--------+
    I expect a bit suggestions.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hint: count the number of correct answers, and compare to the number of questions

    you'll need CASE and GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    nowhere
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    hint: count the number of correct answers, and compare to the number of questions
    you'll need CASE and GROUP BY

    Thanks to you I found out the answer for my question. This query can do what I want.

    SELECT exam_id, (CASE WHEN answer_user.user_answer = questions.true_answer THEN 'Good answer' END) AS 'Check', (CASE WHEN COUNT(question_id)=5 THEN 'Master' ELSE 'Orphan' END) AS 'Divide'
    FROM user JOIN exam, answer_user, questions
    WHERE user.id=exam.user_id
    AND answer_user.exam_id=exam.id
    AND answer_user.question_id=questions.id
    AND answer_user.user_answer = questions.true_answer
    GROUP BY exam_id, 'Check';

    Code:
    +------------+-------------+--------+
    | exam_id    | Check       | Divide |
    +------------+-------------+--------+
    |          1 | Good answer | Orphan | 
    |          8 | Good answer | Orphan | 
    |          9 | Good answer | Orphan | 
    |         10 | Good answer | Orphan | 
    |         11 | Good answer | Orphan | 
    |         12 | Good answer | Orphan | 
    |         13 | Good answer | Orphan | 
    |         15 | Good answer | Orphan | 
    |         16 | Good answer | Orphan | 
    |         18 | Good answer | Orphan | 
    |         20 | Good answer | Orphan | 
    |         21 | Good answer | Orphan | 
    |         23 | Good answer | Master | 
    |         29 | Good answer | Orphan | 
    |         30 | Good answer | Orphan | 
    |         31 | Good answer | Master | 
    |         32 | Good answer | Orphan | 
    +------------+-------------+--------+
    Thanks again


Bookmarks

Posting Permissions

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