I got the following query:
CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
SELECT
n AS nr
, SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
, (
SELECT name FROM category
WHERE category.id = catid
) AS name
, (
SELECT path FROM category
WHERE category.id = catid
) AS path
FROM
(SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
FROM category
CROSS JOIN (SELECT @rownum := 0) r
) AS tmp
GROUP BY catid
ORDER BY
n
);
SELECT * FROM t1;
DROP TEMPORARY TABLE t1;
Result is:
*nr* *catid* *name* *path*
1 426 Computers /426
2 427 Other accessories /426/427
3 428 Laser printers /426/427/428
This is almost good, just need to merge the name column with delimiters.
So final query is:
CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
SELECT
n AS nr
, SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
, (
SELECT name FROM category
WHERE category.id = catid
) AS name
, (
SELECT path FROM category
WHERE category.id = catid
) AS path
FROM
(SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
FROM category
CROSS JOIN (SELECT @rownum := 0) r
) AS tmp
GROUP BY catid
ORDER BY
n
);
/* SELECT * FROM t1; */
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
DROP TEMPORARY TABLE t1;
We get the following query result:
path_long
/Computers/Other accessories/Laser printers
I think you are making this much more complicated than needed. If I understand the requirements, you want a single state, stored in the state
table to cat as the default state value for all persons. And if that default state is changed, the change should be reflected in all - existing and future - rows of person
.
If the above interpretation is correct, then you don't need to store a default in persons
(and then update it every time the default changes). Store NULL
instead! This is how it will work:
CREATE TABLE state
( state_name VARCHAR(20),
population INTEGER
);
INSERT INTO state VALUES ('Montana', 2000000);
CREATE TABLE person
( person_name VARCHAR(50),
person_state VARCHAR(20) DEFAULT NULL, -- yes!
salary INTEGER
);
INSERT INTO person
VALUES
('Alex', NULL, 12000),
('Bill', 'New York', 145000),
('Cath', 'Mississippi', 5000),
('Dean', NULL, 1000) ;
Then we use COALESCE()
to get the default value from state
if person_state
is null:
SELECT p.person_name,
COALESCE(p.person_state, s.state_name) AS person_state,
p.salary
FROM person AS p
CROSS JOIN state AS s ;
person_name | person_state | salary
:---------- | :----------- | -----:
Alex | Montana | 12000
Bill | New York | 145000
Cath | Mississippi | 5000
Dean | Montana | 1000
Change the default state:
DELETE FROM state;
INSERT INTO state VALUES ('Alabama', 50000);
And see the results:
SELECT p.person_name,
COALESCE(p.person_state, s.state_name) AS person_state,
p.salary
FROM person AS p
CROSS JOIN state AS s ;
person_name | person_state | salary
:---------- | :----------- | -----:
Alex | Alabama | 12000
Bill | New York | 145000
Cath | Mississippi | 5000
Dean | Alabama | 1000
dbfiddle here
Best Answer
Test: