Home » SQL & PL/SQL » SQL & PL/SQL » How to form this query
How to form this query [message #207277] Mon, 04 December 2006 22:52 Go to next message
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.
Re: How to form this query [message #207313 is a reply to message #207277] Tue, 05 December 2006 00:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: How to form this query [message #207316 is a reply to message #207277] Tue, 05 December 2006 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
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
Re: How to form this query [message #207371 is a reply to message #207316] Tue, 05 December 2006 04:55 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks, guys!
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:
  


Current Time: Fri Dec 02 20:45:14 CST 2016

Total time taken to generate the page: 0.05483 seconds