How to join tables when one of them doesn't have any relationship with the others

Question

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.

Answer 1

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 <= 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

Answer 2

Rather than untangle what you have, I suggest you start over, at least in thinking this through...

First focus on "Entities":

Put information specific to each author in the Authors table. Include some kind of unique id, say, author_id.

Put information specific to each product in the Products table. Include some kind of unique id, say, product_id.

etc.

Do not store redundant information.
Do not store any "lists" of things; we will get to that in "relationships".

Now focus on "Relationships":

For "Many-to-many": Make a separate table with a pair of ids to point to the two Entities being connected. Many-to-many example: An Author may write many Books; a Book may have many Authors. The relation table would have both author_id and book_id.

For "Many-to-one": An single id in one table connects it to the other Entity. Example: A Country has many Cities, but a City is in only one Country. The City table would contain a column country_id.

Discipline in laying out "Entities" and "Relationships" can make designing this type of database easier.