-3

I'm new to SQL and couldn't find anything on my issue:

This is a simplification of my issue, but basically I have 2 tables I want to join on some column like 'fruit', but table 1 has some rows where fruit is separated by commas in the row, is there a way to join the tables as if the fruit were in different rows for the second table? (I just arbitrarily filled in count)

TABLE 1

count |       fruit | 

1     |  apple, berry

1     |  berry

1     | banana

1     | orange, banana

TABLE 2

count | fruit | 

1     |  apple

4     |  berry

15     | banana

11     | orange
Strawberry
  • 33,750
  • 13
  • 40
  • 57
user1098432
  • 17
  • 1
  • 5
  • 1
    Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Feb 28 '20 at 15:03
  • This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/3404097) – philipxy Feb 28 '20 at 15:37
  • 1
    Comma-separated values are a horrible way to design a database. MySQL features `FIND_IN_SET` (https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set) to deal with such lists. If you can't change the table design, you may want to look into this. – Thorsten Kettner Feb 28 '20 at 15:40
  • Does this answer your question? [MYSQL join comma separated query](https://stackoverflow.com/questions/16923471/mysql-join-comma-separated-query) – philipxy Feb 28 '20 at 15:48
  • PS Just googling your title as is immediately gives a pile of SO hits. – philipxy Feb 28 '20 at 15:54

2 Answers2

2

This may be help you, Here i have used find in set function in inner join from this you can easily join 2 tables with comma separated values

Table Schema - 

CREATE TABLE Table1(
  count_val INT,fruit TEXT);

CREATE TABLE Table2(
  count_val INT,fruit TEXT);  

INSERT INTO Table1(count_val, fruit)
VALUES (1,'apple, berry'),(1,'berry'),(1,'banana'),
       (2,'orange, banana');

INSERT INTO Table2(count_val, fruit)
VALUES (1,'apple'),(1,'berry'),(1,'banana'),
       (2,'orange');

Sql Query-

SELECT * FROM Table2 AS T2
INNER JOIN Table1 AS T1 ON find_in_set(T2.fruit,T1.fruit)

DB Fiddle Link - Here

Meet Soni
  • 130
  • 7
0

You should never join on a text colum, you should always add an ID (Integer, which will be your primary key) to your colum for example

TABLE 1
ID       COUNT     FRUIT
1              1                 apple
2              2                 berry
3              3                 banana
4              1                 orange

And for table 2 the same

for all the rows where you have fruits seperated by a comma you should add an extra row.

then if let's say apple already exists you can either do 2 things, see if the apple exists and increase the count by 1, or simple insert another row with apple.

then if you want to join the 2 tables

SELECT T1.count, T1.fruit, T2.count, T2.fruit
FROM table1 T1
INNER JOIN table2 T2 ON T1.id = T2.id;

If you want to count the amount of a particular food use SUM() functin in SQL

Niels Van Steen
  • 188
  • 1
  • 17
  • This doesn't answer the question. But the question is an obvious duplicate, so it should be closed not answered. [ask] [answer] [help] – philipxy Feb 28 '20 at 16:26
  • 1
    There's nothing wrong with joining on a text column. After all, a date is really just a string with some funny rules attached to it. – Strawberry Feb 28 '20 at 21:30