How to join tables when one of them doesn't have any relationship with the others First of all, I know what I am about to show you is a completely DB mess but I just got it like this.

Tables :

Authors

manufacturers_id manufacturers_name
1                 Name1
2                 Name2
3                 Name3




Authors_to_manufacturers (useless table since there is no authors_id)

authors_id  manufacturers_id
1              100
2              200




manufacturers 

manufacturers_id   manufacturers_name
 1                     Name1
 2                     Name2
 3                     Name2
 ...100                Name1,Name2
 101                   Name1,Name2,Name3
 102                    NameX




products

  products_id  products_name manufacturers_id (This is the PK from manufacturers, not authors)
    1         product1         100
    2         product2         300
    3         product3          1


The diference between authors and manufacturers is that authors table are "single" authors while manufacturers can be more than 1 authors together. Also manufacturers table contains all the exactly info of authors but with more rows (because some books have more than 1 author).

Authors table is screwed, since the primary key is the same as the manufacturers (can't change it now, it will mean a lot more of work that I am not authorized to do) and authors_to_manufacturers (It was suppose to be the connection for both tables, but whoever made this put authors_id when there is none as you could see).

Re-formulating my question:

How to make a query than join tables when one of the tables doesn't have any relation with the others (also the primary key is the same as one of the others tables) 

This is what i got so far

SELECT count(products_id), authors.manufacturers_name
FROM products
INNER JOIN authors ON authors.manufacturers_id = products.manufacturers_id
WHERE authors.manufacturers_name = (select manufacturers_name from manufacturers) group by manufacturers_name;


of course this doesn't work because "subquery has more than one row", but thats exactly what i want (like if it would be ("...where manufacturers_name = manufacturers.name..."), so it would match all the manufacturers name from one table that match with the other table.
How about this:

SELECT Authors.manufacturers_id AS AuthorID, Authors.manufacturers_name AS AuthorName, count(products.products_id) AS '# of Products'
FROM Authors
LEFT JOIN
(
    SELECT manufacturers_id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.manufacturers_name, ',', n.n), ',', -1) value
      FROM Manufacturers t CROSS JOIN 
    (
       SELECT a.N + b.N * 10 + 1 n
         FROM 
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        ORDER BY n
    ) n
     WHERE n.n &lt;= 1 + (LENGTH(t.manufacturers_name) - LENGTH(REPLACE(t.manufacturers_name, ',', '')))
     ORDER BY value
) AS manufacturers ON manufacturers.value = Authors.manufacturers_name
LEFT JOIN products ON manufacturers.manufacturers_id = products.manufacturers_id
GROUP BY Authors.manufacturers_id, Authors.manufacturers_name