MYSQL wrong results output

Question

I have 3 tables with the following data:

1st table called connections where connections.username1 is the one who follows and connections.username2 is the one who is followed.

It has the following rows:

connections.username1 | connections.username2
      mikha           |          guy
      guy             |          maricela
      maricela        |          guy

2nd table called questions. It has a column for for the asker called questions.asker_username and another for the one who receives the question called questions.target_username. When the asker is called "sys.tem" and the target called "every.one", it's considered a global question and could be answered by all members.

Anonymous users could ask and their ip is recorded as the asker_username.

It has the following rows:

questions.id | questions.asker_username | questions.target_username | questions.question
  1          |      mikha               |       guy                 | what's your name?                             
  2          |      mikha               |       maricela            | What's your age?
  3          |      guy                 |       mikha               | what's your name?
  4          |      maricela            |       guy                 | favorite food?
  5          |      xx.xx.xxx.xx        |       mikha               | favorite pet?
  6          |      xx.xx.xxx.xx        |       guy                 | first name?
  7          |      xx.xx.xxx.xx        |       maricela            | first name?   
  8          |      sys.tem             |       every.one           | what's ur name?
  9          |      sys.tem             |       every.one           | favorite movie?  
 10          |      sys.tem             |       every.one           | favorite game? 

The 3rd table is called answers. The id in the answers table is the same as the question id. This table has a column for id and username and answer.

answers.id  |  answers.username | answers.answer
   1        |       guy         | my name is guy
   2        |     maricela      | my name is maricela
   3        |       mikha       | my name is mikha
   4        |       guy         | pizza        
   8        |       guy         | guy is my name
   8        |       maricela    | maricela is my name   
   9        |       maricela    | avatar

I want a query which combines the following conditions related to "mikha" and the people he follows:

1) questions.asker_username is NOT "mikha"

2) questions.target_username is either "mikha" or any of the users he follows.

3) If questions.target_username equals to "every.one" and answered by "mikha", show the question.

4) If questions.target_username equals to "every.one" and answered by any of the people whom "mikha" follows, show the question and its answer. If no answer by the users whom "mikha" follow, don't show the question.

5) If questions.target_username equals to "every.one" and is not answered by any one at all, show the question once.

6) If questions.target_username equals to "every.one" and is not answered by "mikha" and not answered by any of the people he follows, show the question only once.

I use the following query:

SELECT questions.id,answers.id,questions.asker_username,questions.target_username,
    answers.username,questions.question,answers.answer 
FROM questions 
    LEFT JOIN answers ON (questions.id = answers.id) 
    LEFT JOIN connections ON connections.username1 = 'mikha' 
        AND (questions.target_username = connections.username2 
            OR questions.asker_username = connections.username2 
            OR connections.username2 = answers.username) 
WHERE questions.asker_username <> 'mikha' 
    AND (questions.target_username = 'mikha' 
        OR questions.target_username = connections.username2 
        OR (questions.target_username = 'every.one' 
            AND (answers.username = 'mikha' 
                OR answers.username = connections.username2
                OR answers.username IS NULL)
            )
        ) 
GROUP BY questions.id,answers.username

The result I expect:

questions.id | answers.id | questions.asker_username | questions.target_username | answers.username | questions.question | answers.answer
    3        |      3     |        guy               |          mikha            |    mikha         | what's your name?  | my name is mikha
    4        |      4     |        maricela          |          guy              |    guy           | favorite food?     | pizza
    5        |      5     |        xx.xx.xxx.xx      |          mikha            |    NULL          | favorite pet?      | NULL
    6        |      6     |        xx.xx.xxx.xx      |          guy              |    NULL          | first name?        | NULL        
    8        |      8     |        sys.tem           |         every.one         |    NULL          | what's ur name?    | NULL 
    8        |      8     |        sys.tem           |         every.one         |    guy           | what's ur name?    | guy is my name
    9        |      9     |        sys.tem           |         every.one         |    NULL          | favorite movie?    | NULL       
    10       |      10    |        sys.tem           |         every.one         |    NULL          | favorite game?     | NULL 

The result I actually get:

 questions.id | answers.id | questions.asker_username | questions.target_username | answers.username | questions.question | answers.answer
    3        |      3     |        guy               |          mikha            |    mikha         | what's your name?  | my name is mikha
    4        |      4     |        maricela          |          guy              |    guy           | favorite food?     | pizza
    5        |      5     |        xx.xx.xxx.xx      |          mikha            |    NULL          | favorite pet?      | NULL
    6        |      6     |        xx.xx.xxx.xx      |          guy              |    NULL          | first name?        | NULL        
    8        |      8     |        sys.tem           |         every.one         |    guy           | what's ur name?    | guy is my name           
    10       |      10    |        sys.tem           |         every.one         |    NULL          | favorite game?     | NULL 

I built a scheme on http://sqlfiddle.com/#!2/29929e/1 to show you the results I actually get

Thanks :)

Answer 1

The problem is that - in some circumstances - you want to show a question twice when only one matching answer exists. I quote:

So, i want to show it only once for "mikha" whether it's answered or not and show it again each time it's answered by any of the people "mikha" follows

This duplication makes things pretty hard.

I tried to solve that with a UNION and it seems to work. However, I still haven't fully understood your requirements...

Anyway, here we go:

select * from
(
  select
    q.id as q_id, a.id as a_id, q.asker_username,
    q.target_username, a.username, q.question, a.answer
  from
    questions q
    left outer join answers a on q.id = a.id
  where
    q.asker_username <> 'mikha' 
    and
    (
      q.target_username = 'mikha'
      or q.target_username in
         (select username2 from connections where username1 = 'mikha')
      or
      (
        q.target_username = 'every.one'
        and
        (
          a.username = 'mikha'
          or a.username in
             (select username2 from connections where username1 = 'mikha')
          or a.id is null
        )
      )
    )
  union
  select
    q.id as q_id, NULL as a_id, q.asker_username,
    q.target_username, NULL, q.question, NULL
  from
    questions q
  where
    q.asker_username <> 'mikha' 
    and q.target_username = 'every.one'
    and not exists (select id
                    from answers
                    where
                      id = q.id
                      and username = 'mikha'
                    )
) r
order by q_id;

Test it live:
With answer from mikha for question 8
Without answer from mikha for question 8

Answer 2

I would suggest that one of the main issues with your starting point is that you need to join your connections table twice - once for answers and once for questions.

The code below gives inline comments on what it's doing. I would also agree with other answers in suggesting that numeric id fields would be better for comparisons - and I would also advise putting a unique rowid field on the answers table (better still, change id to question_id and then make id the unique field.

SELECT DISTINCT q.id,a.id,q.asker_username,q.target_username,a.username,q.question,a.answer
FROM questions q
  /* Answers */
  LEFT JOIN answers a ON (q.id=a.id)
  /* connection entries where the person being followed is the target */
  LEFT JOIN (SELECT username2 FROM connections WHERE username1='mikha') c_q
    ON c_q.username2=q.target_username
  /* connection entries where the person being followed answered the question */
  LEFT JOIN (SELECT username2 FROM connections WHERE username1='mikha') c_a
    ON c_a.username2=a.username 
  /* Own answers */
  LEFT JOIN (SELECT id FROM answers WHERE username='mikha') a_own 
    ON (q.id=a_own.id) 
  WHERE 
  /* Asker not mikha, target is mikha or followed user - rules 1,2 */
  (q.asker_username <> 'mikha'
  AND (q.target_username='mikha' OR c_q.username2 IS NOT NULL))
  OR
  /* sys.tem/every.one, answered by mikha - rule 3 */
  (q.target_username='every.one' AND a.username='mikha')
  OR
  /* Rules 4, 5 & 6 combine to give "show the answer at least once, and once for every followed user who answered" - here we select any every.one messages where mikha didn't answer and nor did the people he follows */
  (q.target_username='every.one' AND a_own.id IS NULL AND c_a.username2 IS NULL);

It doesn't give you two rows for question 8, but I couldn't really work out why you wanted that. The issue there is that it's not clear which of your rules are additive and which are combinatory. If you can explain which rules should "add" a row rather than just providing another reason to show a row, maybe we can get you that extra row for question 8 too.

Answer 3

OK, lets start from the simplest one (Your first rule):

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id  
WHERE q.asker_username <> 'mikha' 
GROUP BY q.id,a.username

Now lets add Your second rule - now more complexity is added...

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
GROUP BY q.id,a.username

Now the third rule (for everyone answered by mikha):

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
GROUP BY q.id,a.username

Now for the fourth rule:

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
        OR (q.target_username = 'every.one' AND a.username IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
GROUP BY q.id,a.username

Fifth rule (Jesus!):

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
        OR (q.target_username = 'every.one' AND a.username IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
        OR (q.target_username = 'every.one' AND a.answer IS NULL)
GROUP BY q.id,a.username

And for the last one:

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
        OR (q.target_username = 'every.one' AND a.username IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
        OR (q.target_username = 'every.one' AND a.answer IS NULL)
        OR (q.target_username = 'every.one' AND a.username NOT IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
GROUP BY q.id,a.username

I think that rule 4 and rule 6 are kinda against each other (contradicting could be said) and when used in one query it would have the same effect as if omitted...

I didn't test any of the queries but I believe they work.