0

I want to get the values of a row using joining.

I have a category table like this:

id | category_name
1  | books
2  | car
3  | Furniture

and another product table like this:

id | product_name | category_id
1  | The little Bee | 2
2  | New Year  | 1,3

I want to call the product table and output the category_name instead of the category_id. But not only that. I want the category_id that has more than one category e.g New Year has category_id 1 and 3, it should show Book, Furniture as the category_name.

This is my code:

   SELECT p.*, c.*
   FROM products p, category c
   WHERE p.category_id = c.id

But I am only getting one category for categories with more than one. How can I get the complete categories? I guess it deals with joining but I'm not sure.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Wicfasho
  • 311
  • 2
  • 13
  • 1
    The reason it is difficult is that databases are not designed to work well with delimited lists. That structure usually results in overly complex and poor performing queries. The best option is to normalize the table. Search the archives. The topic of [normalizing such tables](http://stackoverflow.com/questions/9944754/storing-ids-as-comma-separated-values/9946106#9946106) and [less ideal work-around](http://stackoverflow.com/questions/25437697/sql-query-multiple-tables-with-multiple-joins-and-column-field-with-comma-separ) has been well covered. Voting to close. – Leigh May 08 '17 at 14:25
  • 1
    http://stackoverflow.com/questions/17927131/is-it-possible-to-have-a-mysql-column-containing-multiple-values-as-foreign-keys Not that I condone this activity... 3rd Normal form says this shouldn't exist in the DB. You should have a junction/associative table; not multiple keys in a single field. But in the interest of helping if the structure is fixed... – xQbert May 08 '17 at 15:30
  • Your `category` of `book` has an id of `1`, but you lack a record with `category_id` of `1` in your `product` table. You have a record where the `category_id` is `1,3`, but `"1" <> "1,3"`. Your schema does not support the join you are trying to make. Because it's bad design, I would suggest changing your schema, not your SQL. – JNevill May 08 '17 at 16:44
  • `SELECT p.*, pu.product_unit_name AS product_unit_name, GROUP_CONCAT(pc.product_category_name) AS product_category_name FROM products p LEFT JOIN product_units pu ON pu.product_unit_id = p.product_unit_id LEFT JOIN product_category pc ON FIND_IN_SET(pc.product_category_id, p.product_category_id) WHERE p.product_status_id = 1 GROUP BY p.product_id, pu.product_unit_name ORDER BY p.product_name` I was able to use that to achieve what i wanted. Sorry for the duplicate question. But the I am not confortable with the way it's been outputted in my table. – Wicfasho May 08 '17 at 22:45
  • It continues to list the output with a `","` stretching the column width. I added some css but not working – Wicfasho May 08 '17 at 22:48

0 Answers0