MySQL SELECT via xref table plus get count of another table's rows

Sorry about the title. I'm not sure how to properly describe the problem.

I have four tables, tables A, B, X and D. A and B have a many-to-many relationship so I'm using X as the link table.

Here's the structure:

Assuming all I have is an ID corresponding to a row in table A, I want to select the rows in table B that match up with that ID plus a count of all rows in table D that have the same b_id. Eh, I suck at explaining in words.

Here's what I would like (all I have to search with is an ID which corresponds to a row in table A -- let's just say I have an "A"):

-------------------------------------------------------------
| b.id | (A COUNT of how m开发者_JS百科any rows in D have a b_id = b.id) |
-------------------------------------------------------------
|  1   |                       20                           |
-------------------------------------------------------------
|  4   |                       12                           |
-------------------------------------------------------------

So, according to the above results, this particular "A" has two "B"s. One of those "B"s has 20 "D"s and the other has 12 "D"s.

How can I write a single query to give me the results I'm after (again, all I'm searching with is an ID in table A)?

Try this:

SELECT b.id, COUNT(1)
  FROM a,x, b,c
    WHERE a.id = <YOUR_ID_FOR_A>
     AND a.id = x.id 
     AND  x.b_id = b.id
     AND b.id = d,b_id
     GROUP BY b.id

If the table x has b_id entries that must exist in table b then you can by pass one join and use the query below:

SELECT b.id, COUNT(1)
  FROM a,x,c
    WHERE a.id = <YOUR_ID_FOR_A>
     AND a.id = x.id 
     AND  x.b_id = d,b_id
     GROUP BY b.id

EDIT: Corrected the typo, changed . to , as column separator.

try

SELECT A.id, B.id, COUNT(B.id) AS cnt
FROM A
INNER JOIN X ON A.id = X.a_id
INNER JOIN B ON X.b_id= B.id
INNER JOIN D ON B.id = D.b_id
GROUP BY B.id