MYSQL输出结果错误

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