CODEWITHSUNDEEP
sqloracle-database
Rony Nguyen
Rony Nguyen

Reputation: 1144

Write a query to return parent location base on level

I have a table structure like this location

id Name is_active level1 level2 level3 level4
1 A true A null null null
2 A>B true A B null null
3 A>B>C true A B C null
4 A>B>C>D true A B C D

if the given location id = 3 then query should return records have id= (1,2,3) but 4

if the given location id = 2 then the query should return records have id= (1,2) but 2 and 4

if the given location id = 4 then the query should return all records have id= 1,2, 3, and 4

appreciate any help ;)

Upvotes: 0

Views: 71

Answers (3)

ahmed
ahmed

Reputation: 9181

Supposing that the last character in the Name indicates the current level, and the character before the last indicates the parent level, you could use the typical way to solve this problem using a recursive query.

with cte(id, parent_lvl, current_lvl) as
(
  select id, substr(reverse(name), 3, 1) parent_lvl, substr(name, -1) current_lvl
  from tbl
  where id = 3
  union all
  select t.id, substr(reverse(t.name), 3, 1), substr(t.name, -1)
  from tbl t join cte c
  on c.parent_lvl = substr(t.name, -1)
)
select id from cte

demo

Upvotes: 0

Aconcagua
Aconcagua

Reputation: 25516

Edit: Stupid me... Seeing the other answer I get aware that CONCATenating all these COALESCEs results results in the name anyway, resulting in:

SELECT * FROM locations
  WHERE id = referenceId
  OR STARTS_WITH((SELECT name FROM locations WHERE id = referenceId), name)
  OR (SELECT name FROM locations WHERE id = referenceId) LIKE CONCAT(name, '%')

Just one of the ORs is needed, of course...

My approach:

SELECT * FROM locations
  WHERE id = referenceId OR
  STARTS_WITH
  (
    (SELECT CONCAT(level1, COALESCE(CONCAT('>', level2), ''), COALESCE(CONCAT('>', level3), '')) FROM locations WHERE id = referenceId),
    name
  )

alternatively

SELECT * FROM locations
  WHERE id = referenceId OR
    (SELECT CONCAT(level1, COALESCE(CONCAT('>', level2), ''), COALESCE(CONCAT('>', level3), '')) FROM locations WHERE id = referenceId)
    LIKE CONCAT(name, '%')

Both working fine (demo/demo) and currently best that comes to my mind.

Admitted, both require inserting the id into query twice, which might be avoided by a join, but that likely comes more expensive.

If either of doesn't work on your database (tested with postgreSQL only) for any reason here yet my previous attempts:

WITH levels AS (
  SELECT
    level1 AS l1,
    CONCAT(level1, COALESCE(CONCAT('>', level2), '')) AS l2,
    CONCAT(level1, COALESCE(CONCAT('>', level2), ''), COALESCE(CONCAT('>', level3), '')) AS l3
  FROM locations WHERE id = referenceId
)
SELECT * FROM locations
  WHERE id = referenceId
  OR name = (SELECT l1 FROM levels)
  OR name = (SELECT l2 FROM levels)
  OR name = (SELECT l3 FROM levels)
)

Works, too, but requires calculating more strings and more comparisons, thus less efficient.

For the case that WITH might not be supported I managed to do it this way, too:

SELECT * from location WHERE id = referenceId OR name IN
(
    SELECT level1
      FROM location WHERE id = referenceId
  UNION
    SELECT CONCAT(level1, COALESCE(CONCAT('>', level2), ''))
      FROM location WHERE id = referenceId
  UNION
    SELECT CONCAT(level1, COALESCE(CONCAT('>', level2), ''), COALESCE(CONCAT('>', level3), ''))
      FROM location WHERE id = referenceId
)

which was my initial attempt – though with all these subqueries it pretty sure can't be too efficient any more...

Upvotes: 1

MT0
MT0

Reputation: 167822

You can compare the names:

SELECT *
FROM   table_name
START WITH id = 3
CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)

or:

SELECT *
FROM   table_name
START WITH id = 3
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)

or:

SELECT *
FROM   table_name t
WHERE  EXISTS(
         SELECT 1
         FROM   table_name x
         WHERE  x.name LIKE t.name || '%'
         AND    x.id = 3
       )

or you can compare the levels:

SELECT *
FROM   table_name t
WHERE  EXISTS(
         SELECT 1
         FROM   table_name x
         WHERE  ( t.level4 = x.level4 OR t.level4 IS NULL )
         AND    ( t.level3 = x.level3 OR t.level3 IS NULL )
         AND    ( t.level2 = x.level2 OR t.level2 IS NULL )
         AND    t.level1 = x.level1
         AND    x.id = 3
       )

Which, for the sample data:

CREATE TABLE table_name (id, Name, is_active, level1, level2, level3, level4) AS
SELECT 1, 'A',       'true', 'A', null, null, null FROM DUAL UNION ALL
SELECT 2, 'A>B',     'true', 'A', 'B',  null, null FROM DUAL UNION ALL
SELECT 3, 'A>B>C',   'true', 'A', 'B',  'C',  null FROM DUAL UNION ALL
SELECT 4, 'A>B>C>D', 'true', 'A', 'B',  'C',  'D'  FROM DUAL UNION ALL
SELECT 5, 'A>E',     'true', 'A', 'E',  NULL, NULL FROM DUAL UNION ALL
SELECT 6, 'A>E>C',   'true', 'A', 'E',  'C',  NULL FROM DUAL UNION ALL
SELECT 7, 'A>E>C>D', 'true', 'A', 'E',  'C',  'D'  FROM DUAL;

All output:

ID NAME IS_ACTIVE LEVEL1 LEVEL2 LEVEL3 LEVEL4
1 A true A null null null
2 A>B true A B null null
3 A>B>C true A B C null

fiddle

Upvotes: 3

Related Questions