Home>

I'm thinking of a sql requesting a one-to-many search,
Could you help me because I got stuck?

About blog tags and author search.

● blog_main
id
title
status

blog_main

id title status
1 Blue book 1
2 Green book 1
3 Red book 1

● blog_tag
tagid
blogid // id of blog_main

blog_tag

tagid blogid
1 1
2 1
2 2
2 3
3 1
4 1
4 2

● blog_auther
autherid
blogid // id of blog_main

blog_auther

autherid blogid
1 1
2 1
2 2
2 3
3 1
4 1
4 2

Multiple "blog_tag" and "blog_auther" are recorded in "blog_main".
I want to create a sql with tagid 2,4 registered and autherid1,4 registered. Is there any good idea?

Currently I have come up with a sql that only searches for "tag", but I can't think of anything that adds "auther" to it.

The following is a tag-only sql. I am using mysql.

select * from blog_main AS t1 where
exists
(
  select 1 from blog_tag AS t2
  where t1.id = t2.blogid and t2.tagid in (2, 4) having count (*) = 2
);
  • Answer # 1

    Simply adding conditions in the same way?

    select * from blog_main AS t1
    where exists (
            select 1 from blog_tag AS t2
            where t1.id = t2.blogid and t2.tagid in (2, 4) having count (*) = 2
          )
      and exists (
            select 1 from blog_auther AS t2
            where t1.id = t2.blogid and t2.autherid in (1, 4) having count (*) = 2
          )

  • Answer # 2

    select
      *
    from
      blog_main bm
    where
      exists (
        select
          1
        from
          blog_tag bt
        where
          bm.id = bt.blogid
        and
          bt.tagid in (2, 4)
      )
    and
      exists (
        select
          1
        from
          blog_auther ba
        where
          bm.id = ba.blogid
        and
          ba.autherid in (1, 4)
      )
    ;


    Can you close like this? . . ?