CODEWITHSUNDEEP
phpmysqlsqljoin
Matej Vrzala M4
Matej Vrzala M4

Reputation: 1374

SQL JOIN multiple ID's

First Table

|product_name   |   category_ids  |
|---------------------------------|
|- apple        |   1, 2          |
|- extra_apple  |   1, 3          |

Second Table

|category_id  |   category_name   |
|---------------------------------|
|- 1          |   fruit           |
|- 2          |   cheap           |  
|- 3          |   expensive       |

How can I join this, so I get something like this

|     product_name  |     category_names   |
--------------------------------------------
|     apple         |     fruit, cheap     |
|     extra_apple   |     fruit, expensive |

Upvotes: 1

Views: 716

Answers (2)

Himanshu Singh
Himanshu Singh

Reputation: 91

First table is not Normalize

|product_name   |   category_ids  |
|---------------------------------|
|- apple        |   1, 2          |
|- extra_apple  |   1, 3          |

First Normalize this Table AS

|product_name   |   category_ids  |
|---------------------------------|
|- apple        |   1             |
|- apple        |   2             |
|- extra_apple  |   1             |
|- extra_apple  |   3             |

Second Table

|category_id  |   category_name   |
|---------------------------------|
|- 1          |   fruit           |
|- 2          |   cheap           |  
|- 3          |   expensive       |

MySQL Syntax as: "SELECT T1.product_name, T2.category_name FROM FIRST_TABLE AS T1, SECOND_TABLE AS T2 WHERE T1.category_ids=T2.category_id"

This will give you result as:

|     product_name  |     category_names   |
--------------------------------------------
|     apple         |     fruit            |
|     apple         |     cheap            |
|     extra_apple   |     fruit            |
|     extra_apple   |     expensive        |

Upvotes: 1

juergen d
juergen d

Reputation: 204746

To make it right, add another table:

product_categories table
------------------------
product_id
category_id

It contains one record for each category a product has.

Upvotes: 3

Related Questions