First of all, I know what I am about to show you is a completely DB mess but I just got it like this.
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.