OraFAQ Forum: SQL & PL/SQL » How to form this query

Home » SQL & PL/SQL » SQL & PL/SQL » How to form this query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to form this query [message #207277]

Mon, 04 December 2006 22:52

a_developer
Messages: 194
Registered: January 2006

Senior Member

I have 2 tables: Parent and Child.

Parent

HQ_ID  Has_Child
-----  --------- 
A         N
B         N
C         Y

Child

HQ_ID  Unit
-----  ----
C        C1
C        C2 
C        C3

I need a query that returns the HQ if it has no child, else return the children. The returned values should be like:

Stores
------
A
B
C1
C2
C3

Thanks in advance.

Report message to a moderator

Re: How to form this query [message #207313 is a reply to message #207277]

Tue, 05 December 2006 00:50

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

The data type needs to be same for the above mentioned columns.
Then you can use UNION ALL (to retain the duplicates).

Select from master if there are no records in the child using not exists
union all
select from child.

I think you can write the query now.

By
Vamsi

Report message to a moderator

Re: How to form this query [message #207316 is a reply to message #207277]

Tue, 05 December 2006 00:51

Frank
Messages: 7901
Registered: March 2000

Senior Member

This can be done in several ways. The has_child column seems to be an abundant column. No need to keep track of that in a separate column, since you can query the client table for that.
So I provided a way to use the has_child and a way without using this column.

SQL> drop table child;

Table dropped.

SQL> drop table parent;

Table dropped.

SQL> 
SQL> create table parent
  2  ( hq_id	 varchar2(1)
  3  , has_child varchar2(1)
  4  );

Table created.

SQL> create table child
  2  ( hq_id	 varchar2(1)
  3  , unit	 varchar2(2)
  4  );

Table created.

SQL> 
SQL> insert into parent (hq_id, has_child) values ('A', 'N');

1 row created.

SQL> insert into parent (hq_id, has_child) values ('B', 'N');

1 row created.

SQL> insert into parent (hq_id, has_child) values ('C', 'Y');

1 row created.

SQL> insert into child (hq_id, unit) values ('C', 'C1');

1 row created.

SQL> insert into child (hq_id, unit) values ('C', 'C2');

1 row created.

SQL> insert into child (hq_id, unit) values ('C', 'C3');

1 row created.

SQL> 
SQL> REM Use has_child as leading column:
SQL> select decode(has_child, 'Y', child.unit, parent.hq_id)
  2  from   parent
  3  ,	    child
  4  where  child.hq_id(+) = parent.hq_id
  5  order  by parent.hq_id
  6  ,	    child.unit;

DE
--
A
B
C1
C2
C3

SQL> 
SQL> REM Not using the has_child
SQL> select nvl(child.unit, parent.hq_id)
  2  from   parent
  3  ,	    child
  4  where  child.hq_id(+) = parent.hq_id
  5  order  by parent.hq_id
  6  ,	    child.unit;

NV
--
A
B
C1
C2
C3

Report message to a moderator

Re: How to form this query [message #207371 is a reply to message #207316]

Tue, 05 December 2006 04:55

a_developer
Messages: 194
Registered: January 2006

Senior Member

Thanks, guys!

Report message to a moderator

Previous Topic:	how to perform query based on data in excel file
Next Topic:	How to retrieve 2nd highest salary from table emp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 11:57:32 CST 2024