MySQL - JOIN based on value of column?

Jan Šafránek

I have two tables:

Table "categories"

category  |  parents
1         |  5,4,1
2         |  3,2

Column parents is group of numbers divided with comma, so it could be used in query as IN(parents)

Table "categories_goods"

item | category
10   | 1
12   | 2

And I want to export data to third table - there will be all parents for every category. Result should be:

Table "categories_goods_all"

item | category
10   | 5
10   | 4
10   | 1
12   | 3
12   | 2

I have this solved in PHP, but it is slow when there is 10000 * x of rows in table categories_goods. So I am looking for pure MySQL solution. Any ideas?

Utsav

Use this

Demo: http://rextester.com/CAF76544

Query

select
  g.item,
  SUBSTRING_INDEX(SUBSTRING_INDEX(c.parents, ',', numbers.n), ',', -1) category1
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers 
 INNER JOIN categories c 
  on CHAR_LENGTH(c.parents)
     -CHAR_LENGTH(REPLACE(c.parents, ',', ''))>=numbers.n-1
  inner join categories_goods g
  on c.category=g.category 
order by
  c.category, n

Thanks to this answer to split csv to rows.

Explanation: The number table generate number 1-5. You might want to add more rows here for if needed for more parent. With its help, you can separate csv to columns. Refer the answer URL given above for the same.

Now you just need to do a join to categories_goods to fetch item corresponding to parent

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL first select column based on parameter value and then value of column

From Dev

Oracle: Is it possible to choose which table to join based on a column value?

From Dev

MySQL - JOIN based on CONVERT a column to NUMERIC

From Dev

MySQL join a different table based on field value

From Dev

MySQL, select column name based on a value

From Dev

mysql How To join 2 column value into 2 column value

From Dev

how to get only one record based on column value in oracle join

From Dev

MySQL join and count according to its column value

From Dev

Mysql: change the column value based on column's previous value

From Dev

SQL inner join on a column based on max value from another column

From Dev

mysql select query php based on column value

From Dev

MySQL - Select last rows of LEFT JOIN column based on the fields

From Dev

Mysql - Populate data in one column based on join with three different fields

From Dev

Increment column value in mysql based on same ID

From Dev

MySQL first select column based on parameter value and then value of column

From Dev

MySQL - JOIN based on CONVERT a column to NUMERIC

From Dev

Join Based On Column Value (Best Match)

From Dev

MySQL Update Value of column Based on DATE

From Dev

Inner Join table based on column value

From Dev

MySQL join and count according to its column value

From Dev

Join type based on column value

From Dev

mysql group records based on a column value

From Dev

Mysql: change the column value based on column's previous value

From Dev

How to write mysql query to achieve the following type of join (Join based on value of a column )

From Dev

Return one row in a view based on a left join column value

From Dev

MySQL - inner join - add column with value based on other value

From Dev

mysql join get column value from comma separated value

From Dev

How to join the table based on main table column value in SQL Server?

From Dev

How to join table based on two column in mysql?

Related Related

  1. 1

    MySQL first select column based on parameter value and then value of column

  2. 2

    Oracle: Is it possible to choose which table to join based on a column value?

  3. 3

    MySQL - JOIN based on CONVERT a column to NUMERIC

  4. 4

    MySQL join a different table based on field value

  5. 5

    MySQL, select column name based on a value

  6. 6

    mysql How To join 2 column value into 2 column value

  7. 7

    how to get only one record based on column value in oracle join

  8. 8

    MySQL join and count according to its column value

  9. 9

    Mysql: change the column value based on column's previous value

  10. 10

    SQL inner join on a column based on max value from another column

  11. 11

    mysql select query php based on column value

  12. 12

    MySQL - Select last rows of LEFT JOIN column based on the fields

  13. 13

    Mysql - Populate data in one column based on join with three different fields

  14. 14

    Increment column value in mysql based on same ID

  15. 15

    MySQL first select column based on parameter value and then value of column

  16. 16

    MySQL - JOIN based on CONVERT a column to NUMERIC

  17. 17

    Join Based On Column Value (Best Match)

  18. 18

    MySQL Update Value of column Based on DATE

  19. 19

    Inner Join table based on column value

  20. 20

    MySQL join and count according to its column value

  21. 21

    Join type based on column value

  22. 22

    mysql group records based on a column value

  23. 23

    Mysql: change the column value based on column's previous value

  24. 24

    How to write mysql query to achieve the following type of join (Join based on value of a column )

  25. 25

    Return one row in a view based on a left join column value

  26. 26

    MySQL - inner join - add column with value based on other value

  27. 27

    mysql join get column value from comma separated value

  28. 28

    How to join the table based on main table column value in SQL Server?

  29. 29

    How to join table based on two column in mysql?

HotTag

Archive