Return count from not direct related tables To clarify what I'm asking:
My main goal was how I can retrieve all Manufacturers with a count of entries next to them.

Working with 3 tables {Manufacturers (wp_manufacturers), Dealers(wp_users), Entries(wp_posts)} [plus a fourth, wp_usermeta, tied to wp_users].


Manufacturers hold brands like Suzuki Honda etc 
Dealers as the users publishing an Entry that showcase a Car of a manufacturer.
Entries is the table that holds an entry with the content and the user who published.


The table Dealers has a field that connects every user with the Manufacturer (actually, currently set up through the fourth table).

What I trying to do is to have for every Manufacturer a count of entries that exist in the Entries table.

A dealer can be only with one manufacturer. Also, a manufacturer can have multiple dealers.

This is original for a WordPress site that is heavily customized and we would like a direct approach to the database.

Given this data, with 2 Manufacturers and 3 dealers:

  Manufacturers           wp_posts
+-----------------+    +-----------------------------------------------+
|id      |name    |    |ID     |post_author    |post_type |post_status |
+-----------------+    +-----------------------------------------------+
|   1    |suzuki  |    | 455   |    13         |  car     | publish    |
|   2    |honda   |    | 689   |    13         |  car     | publish    |
|        |        |    | 1689  |    2          |  car     | publish    |
|        |        |    | 1889  |    13         |  car     | draft      |
|        |        |    | 2090  |    44         |  car     | publish    |
+---^----+--------+    +------------^----------+----------+------------+
    |                           ____|
    |           wp_usermeta    |
    |          +---------+-----v-------------+------------------+
    |          |umeta_id | user_id |meta_key | meta_value       |
    |          +------------------------------------------------+
    |          | 498     |   13     | _manuf  |     1           |
    |          | 709     |   2      | _manuf  |     2           |
    |          | 1029    |   44     | _manuf  |     2           |
    |          |         |          |         |                 |
    |          +------------------------------------------------+
   ++----------------------------------------------^


The results I'd like to get are:

+-------+----------+--------------+
|  id   |   name   |   count      |
+---------------------------------+
|  1    | suzuki   |     3        |
+---------------------------------+
|  3    | honda    |     2        |
+-------+----------+--------------+


I also want to check the post_status to only count entries that have been published.

Today I read again my question and I found indeed not clear. The reason I didn't like to write from the start the table original names was not to be confused with the WordPress solution as I would like only the MySQL approach.

Example on the wp site where I use the tables of wp this returns the data but not with 0 when not an entry found in the wp_post(post_type=car) and also not sure this is the optimal way to do this

 SELECT DISTINCT id, IFNULL(COUNT(id),0),name FROM (
SELECT wp.post_author,wp.post_status,wp.post_type,wu1.meta_value FROM wp_posts wp
  LEFT JOIN (
  wp_users wu
  LEFT JOIN wp_usermeta wu1 ON wu1.user_id=wu.ID
  ) ON wp.post_author=wu.ID 

  WHERE wu1.meta_key='_manuf' AND wp.post_status='publish'  AND wp.post_type='car'
 ) x
 left jOIN manufacturers ma  ON x.meta_value=ma.id

  GROUP BY id
  ;
If your dealers are related to the manufacturers and entries are related to the dealers so you have to JOIN three tables, then GROUP resulting table by manufacturers and COUNT() all entries grouped together:

SELECT w.ID AS manufacturer
     , count( q.ID ) AS counter
  FROM manufacturers AS w
  JOIN dealers       AS z ON z.manuf_ID  = w.ID
  JOIN entries       AS q ON q.dealer_ID = z.ID
 GROUP BY w.ID
 ORDER BY w.ID ASC
;