How do select different columns from my table depending upon a condition?

Question

I wanted to retrieve records from Oracle DB depending upon a column's value. How do I do that..?

I need to select Project ID depending upon 'Is Child Project' column value in the same table. I want to fetch Project IDs of all highest possible level Projects : A parent's ID if current record is child, if current record has no parent then it should return ID of self.

I am using Oracle SQL Developer 18.2.

| Project ID | Is Child Project | Parent Project ID |
+------------+------------------+-------------------+
|        101 | Yes              | 501               |
|        102 | No               | -                 |
|        201 | No               | -                 |
|        202 | Yes              | 502               |
|        501 | No               | -                 |
|        502 | No               | -                 |
+------------+------------------+-------------------+

I expect to get relevant Project ID i.e. if Project is Child then I should get 'Parent Project ID' else just 'Project ID', meaning that

Answer 1

A table that contains a primary key id column, along with another column which is a foreign key to the table's own primary key, is known as an adjacency list.

In the example you cited, it is not necessary to use the "is_child_project" column because that information is redundant. The presence of a value in the parent column is the logical answer to the question of whether a row is the top level of a hierarchy.

Oracle has a few proprietary SQL constructs to help write useful queries against adjacency lists. I have added a few more rows to the ones you have provided, and updated the parent projects of 501 and 502 to show more depth to a hierarchy.

COLUMN p_id_hierarchy FORMAT a14
COLUMN parent_project_id FORMAT a17
COLUMN top_level_project FORMAT a17

WITH ald( project_id, parent_project_id ) AS (--adjacency list data
    SELECT '101', '501' FROM DUAL UNION ALL
    SELECT '102',  NULL FROM DUAL UNION ALL
    SELECT '201',  NULL FROM DUAL UNION ALL
    SELECT '202', '502' FROM DUAL UNION ALL
    SELECT '501', '003' FROM DUAL UNION ALL
    SELECT '502', '002' FROM DUAL UNION ALL
    SELECT '003', '001' FROM DUAL UNION ALL
    SELECT '002', '001' FROM DUAL UNION ALL
    SELECT '001',  NULL FROM DUAL
    )

    SELECT LPAD(project_id, LENGTH(project_id) + LEVEL - 1, ' ') AS p_id_hierarchy,
           parent_project_id,
           CONNECT_BY_ROOT project_id AS top_level_project
      FROM ald
CONNECT BY parent_project_id = PRIOR project_id
START WITH parent_project_id IS NULL
;

From what you've written, it seems your requirement is to know the top-level (root) of any hierarchy in your adjacency list. The CONNECT_BY_ROOT operator will give you this information.

P_ID_HIERARCHY PARENT_PROJECT_ID TOP_LEVEL_PROJECT
-------------- ----------------- -----------------
001                              001              
 002           001               001              
  502          002               001              
   202         502               001              
 003           001               001              
  501          003               001              
   101         501               001              
102                              102              
201                              201              

9 rows selected. 

To be clear, the CONNECT BY, STARTS WITH, and CONNECT_BY_ROOT operations are Oracle-specific. If you want to investigate a more RDBMS-agnostic approach, you will need to research the topic, "Recursive Common Table Expressions". That is an SQL-ANSI compliant method for performing hierarchical queries--and is also available in Oracle.

Hierarchical queries are not a simple topic to learn. It took me quite some time practicing them to build a solid understanding. Good luck.