Xalsar Xalsar - 3 months ago 7
MySQL Question

Access to field information with a list of ids in a inner join

I created a table called

recommendations
that looks like this:

+----+---------------+---------------------+
| id | name | elements_to_announce |
+----+---------------+---------------------+
| 1 | Recomanation1 | 2 |
| 2 | Recomanation1 | 1,2 |
+----+---------------+---------------------+


And another table called
products
that looks like that:

+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Low fat croissant |
| 2 | Yupi bread |
+----+-------------------+


Now I want to create a join with this two tables that gets the information of
recommendations
but replaces the
elements_to_announce
with the names of products.

It has to be something like this:

+----+---------------+---------------------------------------+
| id | name | elements_to_announce |
+----+---------------+---------------------------------------+
| 1 | Recomanation1 | Yupi bread |
| 2 | Recomanation1 | Low fat croissant,Yupi bread |
+----+---------------+---------------------------------------+


I make a lot of tests and the code that works almost fine is that one:

SELECT
r.id AS recommendation_id,
GROUP_CONCAT(p.id) AS product_id
FROM products AS p
JOIN recommendations AS r
GROUP BY r.id


The problem with this code is that it returns all product names.

EDIT:
The result of my "almost fine" query:

+----+---------------+---------------------------------------+
| id | name | elements_to_announce |
+----+---------------+---------------------------------------+
| 1 | Recomanation1 | Low fat croissant,Yupi bread |
| 2 | Recomanation1 | Low fat croissant,Yupi bread |
+----+---------------+---------------------------------------+

Answer

The problem is that you seem to use a relational database in a way it is not meant for. The table recommendations violates the condition of the so-called first normal form. I recommend to split it into two tables:

  • recommendations: id, name
  • recommendationContents: recommendation_id, product_id

And this is how your query will look like:

SELECT r.id, r.name, GROUP_CONCAT(p.name)
FROM recommendationContents AS rc
  JOIN recommendations AS r ON rc.recommendation_id = r.id
  JOIN products AS p ON rc.product_id = p.id
GROUP BY r.id, r.name