Help on a query

Hi,
Iam trying to figure out the query to achieve the output depicted below

create table master (
iss_dtl_seq_nbr int
)

create table child1(
iss_dtl_seq_nbr int,
line_no int
)

create table child2(
iss_dtl_seq_nbr int,
line_no int
)

insert into master
select 1

insert into child1
select 1, 1
insert into child1
select 1, 2

insert into child2
select 1, 1

insert into child2
select 1, 2

insert into child2
select 1, 3

SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
FROM CHILD1, CHILD2, MASTER
WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
AND CHILD1.LINE_NO = CHILD2.LINE_NO
ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO

Expected Output:

ISS_DTL_SEQ_NBR LINE_NO LINE_NO
----- ---- ----
1 1 1
1 2 2
1 NULL 3

Can anybody help with the query to achieve this?

Thanks.On 9 Sep 2004 09:33:21 -0700, Sudhir wrote:

>Hi,
> Iam trying to figure out the query to achieve the output depicted below
>create table master (
>iss_dtl_seq_nbr int
>)
>create table child1(
>iss_dtl_seq_nbr int,
>line_no int
>)
>create table child2(
>iss_dtl_seq_nbr int,
>line_no int
>)
>insert into master
>select 1
>insert into child1
>select 1, 1
>insert into child1
>select 1, 2
>insert into child2
>select 1, 1
>insert into child2
>select 1, 2
>insert into child2
>select 1, 3
>
>SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
>FROM CHILD1, CHILD2, MASTER
>WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
>AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
>AND CHILD1.LINE_NO = CHILD2.LINE_NO
>ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
>Expected Output:
>ISS_DTL_SEQ_NBR LINE_NO LINE_NO
>----- ---- ----
>1 1 1
>1 2 2
>1 NULL 3
>Can anybody help with the query to achieve this?
>Thanks.

Hi Sudhir,

Thanks for providing the statements to recreate your table structure and
data. The following query will produce the expected output:

select coalesce(master.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr) AS
iss_dtl_seq_nbr,
child1.line_no, child2.line_no
from master
inner join child1
on master.iss_dtl_seq_nbr = child1.iss_dtl_seq_nbr
right outer join child2
on child1.iss_dtl_seq_nbr = child2.iss_dtl_seq_nbr
and child1.line_no = child2.line_no
order by child2.line_no, child1.line_no

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this:

select master.iss_dtl_seq_nbr, child1.line_no, child2.line_no
from child1 full outer join child2 on child1.iss_dtl_seq_nbr =
child2.iss_dtl_seq_nbr and child1.line_no = child2.line_no
join master on master.iss_dtl_seq_nbr =
isnull(child1.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr)

Shervin

s.sudhir@.gmail.com (Sudhir) wrote in message news:<80a5355b.0409090833.4ccd6d2b@.posting.google.com>...
> Hi,
> Iam trying to figure out the query to achieve the output depicted below
> create table master (
> iss_dtl_seq_nbr int
> )
> create table child1(
> iss_dtl_seq_nbr int,
> line_no int
> )
> create table child2(
> iss_dtl_seq_nbr int,
> line_no int
> )
> insert into master
> select 1
> insert into child1
> select 1, 1
> insert into child1
> select 1, 2
> insert into child2
> select 1, 1
> insert into child2
> select 1, 2
> insert into child2
> select 1, 3
>
> SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
> FROM CHILD1, CHILD2, MASTER
> WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
> AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
> AND CHILD1.LINE_NO = CHILD2.LINE_NO
> ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
> Expected Output:
> ISS_DTL_SEQ_NBR LINE_NO LINE_NO
> ----- ---- ----
> 1 1 1
> 1 2 2
> 1 NULL 3
> Can anybody help with the query to achieve this?
> Thanks.