[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Create rows for exploded string

Posted on 2014-01-29
7
Medium Priority
?
364 Views
Last Modified: 2014-02-18
Say I have this in Table 1
Category 1/Category 2/Category 3

Would it be possible without getting too convoluted to take that string and look at is if it was
Category 1
Category 1/Category 2
Category 1/Category 2/Category 3

Grab the ID's for those 3 entries from Table 2, and insert into Table 3 using the ID from Table 1 and the ID from Table 2
0
Comment
Question by:Gary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39819360
It looks possible to do it like this:
CREATE TABLE IF NOT EXISTS `test_cat` (
  `category` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test_cat`
--

INSERT INTO `test_cat` (`category`) VALUES
('Category 1/Category 2/Category 3'),
('Category A/Category B/Category C');

SELECT category, substring_index(category, '/', 1) as desired_value FROM `test_cat` 
union
SELECT category, substring_index(category, '/', 2) FROM `test_cat` 
union
SELECT category, category FROM `test_cat` 

Open in new window


Result:
category                                 desired_values
Category 1/Category 2/Category 3	Category 1
Category A/Category B/Category C	Category A
Category 1/Category 2/Category 3	Category 1/Category 2
Category A/Category B/Category C	Category A/Category B
Category 1/Category 2/Category 3	Category 1/Category 2/Category 3
Category A/Category B/Category C	Category A/Category B/Category C

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39819365
Note also that "union" will automatically remove duplicate values from the results. This might be a good thing, but if not you can use "union all"
0
 
LVL 58

Author Comment

by:Gary
ID: 39819497
It's a bit more complicated than that
I've built a fiddle here
http://sqlfiddle.com/#!2/88a0db

What should happen is I get three entries in the product_categories table like so
product_category_id     category_id
1                                         1
2                                         2
3                                         3
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 39819613
I'm still somewhat unclear on whether your test data is similar to your real data. For example, if you have the category_name value 'Category 1/Category 2/Category 3' then do the category_id's 1, 2 and 3 get extracted from the end of each category in the category_name? Or are they sequentially generated, or something else?
0
 
LVL 58

Author Comment

by:Gary
ID: 39820915
Example is wrong it should end up with the following in product_categories
product_id     category_id
1                           1
1                           2
1                           3

Single product with ID's of the corresponding three category entries in the category table - category1, category1/category2 and category1/category2/category3


After my initial this would be great thought I realised I need to do other things with the categories that just wouldn't be possible in an SQL statement.
But in case you have any ideas I'll leave it open a while longer
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39830342
give a try.
INSERT INTO product_categories
SELECT p.product_id,c.category_id
  FROM categories c
  JOIN products p
    ON p.category LIKE CONCAT('%',c.category_name,'%');
SELECT * FROM product_categories;

Open in new window

http://sqlfiddle.com/#!2/49dcf/1
0
 
LVL 58

Author Closing Comment

by:Gary
ID: 39869166
Not sure what is happening here but seems to be working.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question