MySQL、join だけでリレーションに対して絞り込み検索したい

Question

例えば下記のようなデータがあるとして(本来はテーブルですが、便宜上jsonにしています)、
```json
users = [
    {
        "user_name": "userA",
        "posts": [
            {
                "post_name": "postA",
                "comments": [
                    {
                        "body": "foo"
                    },
                    {
                        "body": "bar"
                    }
                ]
            }
        ]
    },
    {
        "user_name": "userB",
        "posts": [
            {
                "post_name": "postB",
                "comments": [
                    {
                        "body": "foo"
                    },
                    {
                        "body": "foo"
                    }
                ]
            }
        ]
    }
]
```


(上記の json データだと紛らわしいとの指摘を受けましたので、以下テーブルデータも併記しました。)
`users` テーブル:
| id | name  |
|----|-------|
| 1  | userA |
| 2  | userB |

`posts` テーブル
| id | user_id  |
|----|----------|
| 1  | 1        |
| 2  | 2        |

`comments` テーブル
| id | post_id  | body |
|----|----------|------|
| 1  | 1        | foo  | 
| 2  | 1        | bar  |
| 3  | 2        | foo  |
| 4  | 2        | foo  |

このデータに対して、以下のように検索したいです。

1. `posts`を持つ`users`;
2. さらにその`posts`が`comments`を持つ;
3. さらにその`comments`の**すべて**は "foo" を含む.

例のデータの場合ですと、上記条件をクリアしたユーザは`userB`となります。


サブクエでできるのは分かりますが、パフォーマンスが良くなく、joinで実現できないでしょうか？
(上記はのサンプルのリレーションシップは3層だけですが、実際はもっとリレーションシップが深いです。)
それか、別のもっといいやり方はあるのでしょうか？

Accepted Answer

こんな感じでしょうか？（あれ？既存の回答と同じかな？）
- 元データ
```SQL
create table users(id int primary key,name varchar(10),index(name));
insert into users values(1,'userA'),(2,'userB'),(3,'userC'),(4,'userD'),(5,'userE');  
create table posts(id int primary key,user_id int,index(user_id));
insert into posts values(1,1),(2,2),(3,3),(4,4); 
create table comments(id int primary key,post_id int,body varchar(30),index(post_id,body));
insert into comments values(1,1,'foo'),(2,1,'bar'),(3,2,'foo'),(4,2,'foo'),(5,3,'foo');
```
※userEはpostsがない
※userDはpostsがあるけどpostsがcommentsを持っていない
※userAはcommentsがfooを含んでいないものを持っている

- 検索
```SQL
select * from users as t1
inner join posts as t2 on t1.id=t2.user_id
and exists(
select 1 from comments
group by post_id
having sum((body regexp 'foo'))=count(*)
and post_id=t2.id
);
```
※ロジックがおかしかったので修正しました

Answer

commentsがあって、'foo'以外が無いものとすれば、commentsが全て'foo'という事になります。
※commentsの有る無しが関係ないなら、最後の条件は不要。
```SQL
select *
from users
where not exists(
       select 1 from posts inner join comments on posts.id=comments.post_id
       where posts.user_id=users.id and comments.body<>'foo'
      )
  and exists(
       select 1 from posts inner join comments on posts.id=comments.post_id
       where posts.user_id=users.id
      ) 
```
追記
--
users の行を求めたいのだから、keyのレベルを合わせるのにはサブクエリーが必須になるんですよね。
一応別パターン
```SQL
select *
from users inner join (
       select user_id from posts inner join comments on posts.id=comments.post_id
       group by user_id 
       having count(*)=sum(case when comments.body='foo' then 1 else 0 end)
     ) cond on cond.user_id=users.id
```

Answer

######修正分

サブクエリで遅いというのは、こんな感じにした場合ことでしょうか？
SQLがあると、アドバイスがつきやすいかもしれません。

```sql
select ...
from users
join posts
  on ...
join (select ... // fooの数とコメントの数が一致していれば、postsとcommentsをjoinできるような要素を出力する
      from comments
      goup by postid) as filterdComments  
  on ...
join comments
  on ...
```

追記

関連した質問