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.