CODEWITHSUNDEEP
sqldatabaseselectoracle11g
ofir
ofir

Reputation: 139

oracle nested select query

I have this table on oracle db:

Original table

Can i do nested select query that change the PARENT column to the name associated with it? like this:

Would like to have table

I have tried:

SELECT PC.ID
,(
  SELECT PC.NAME from PRODUCTS_CATEGORIES PC where PC.PARENT = PC.ID
 )
 ,PC.NAME
  FROM PRODUCTS_CATEGORIES PC

and got this error:

Error Message: ORA-01427: single-row sub query returns more than one row

Upvotes: 6

Views: 82571

Answers (3)

user330315
user330315

Reputation: 

select pc.id,
       coalesce(pr.name, 'No Parent') as parent_name,
       pc.name
from products_categories pc
  left join products_categories pr on pc.parent = pr.id;

Note that storing 0 as the parent isn't such a good idea. It indicates that you probably don't have a foreign key constraint defined on the parent column - which you should. To indicate that no parent is present it's better to use NULL (which would also work when you do have a foreign key constraint).

Upvotes: 2

jenson-button-event
jenson-button-event

Reputation: 18961

Changing the alias in the subquery to say PC2 would at least make the query logically easier to read. 

SELECT PC.ID
,(
  SELECT PC2.NAME from PRODUCTS_CATEGORIES PC2 where PC.PARENT = PC2.ID
 )
 ,PC.NAME
  FROM PRODUCTS_CATEGORIES PC

I'm surprised oracle can work out the join since both aliases are PC. other than that the puter he don't lie - some products have more than one parent.

You could if you didnt mind, choose the first parent (if that is indeed the problem):

SELECT PC.ID
,(
  SELECT PC2.NAME from PRODUCTS_CATEGORIES PC2 where PC.PARENT = PC2.ID 
  AND ROWNUM <= 1
 )
 ,PC.NAME
  FROM PRODUCTS_CATEGORIES PC

Upvotes: 0

rs.
rs.

Reputation: 27467

You can do this

SELECT PC.ID,
(
   SELECT DISTINCT PC2.NAME from PRODUCTS_CATEGORIES PC2 
   where PC.PARENT = PC2.ID AND PC2.PARENT = 0
 ),
 PC.NAME
 FROM PRODUCTS_CATEGORIES PC

OR 

 SELECT PC.ID,NVL(PC2.NAME,PC.PARENT) PC2_NAME,PC.NAME
  FROM PRODUCTS_CATEGORIES PC 
  LEFT OUTER JOIN 
  (SELECT DISTINCT ID, NAME 
  from PRODUCTS_CATEGORIES WHERE PARENT = 0) PC2 ON PC.PARENT = PC2.ID

Upvotes: 6

Related Questions