MySQL使用行值计数数据而没有新的查询循环

I've 4 table for a newsletter. Newsletters, Subscribers, Subscriber Groups and Selected Subscriber Groups. I've choose subscriber groups in campaign edit area, and its save selected groups to tbl_newsletter_groups table like;

tbl_newsletters

NID    title     details
 1     text 1    content 1
 2     text 2    content 2

tbl_subscriber_groups

GID   group_name
 5    group 1
 6    group 2

tbl_subscribers

SID   GID  email             name
 10    5   sub1@mail.com     sub1 name
 11    6   sub1@mail.com     sub1 name

tbl_newsletter_groups

NGID    NID   GID
 15      1     6
 16      1     6
 17      1     6

I want to show total selected subscriber count when I list newsletters in my page. My soulution works fine, Im looking for simple and clearly statement, there any faster way available like in single newsletter list statement?

Here my own count style (yes I know its too bad and long way);

$subGID = array();
$list = $myconn->query("SELECT * FROM tbl_newsletters");
while($listRs = $list->fetch_assoc()){
   $grps = $myconn->query("SELECT * FROM tbl_newsletter_groups WHERE NID=". $listRs['NID'] ."");
   while($grpsRs = $grps->fetch_asscoc()){
      $subGID[] = $grpsRs['GID'];
    } $grps->free();
   $subs = implode(" OR GID=",$subGID);
   $count = mysqli_num_rows($myconn->query("SELECT ID FROM tbl_subscribers WHERE GID=". $subs));
   echo('Total Selected Subscriber: '.$count);
} $list->free();

Thanks.

The search term you want is "set-based logic".

Your thinking is sound: you need everything from tbl_newsletters, then you need to count results from tbl_subscribers, but in order to get those you need information from tbl_newsletter_groups.

In SQL, that's an indication you want a join. You've already discovered the conditions you need, you just don't know the syntax. A reference manual can help there.

Now you'll have a bunch of records, which you need to smash into a smaller number of records. You need aggregation functions and a GROUP BY clause.

So here's the final query:

SELECT n.NID, n.title, n.details, COUNT(s.SID)
FROM   tbl_newsletters AS n
  JOIN tbl_newsletter_groups AS g ON n.NID = g.NID
  JOIN tbl_subscribers AS s ON g.GID = s.GID
GROUP BY n.NID