How to combine master-child tables as ....?
-
I have one master table
ITEM_NO ITEM_FLAG
====== ==========
1 Y
2 N
3 Y
I have one child table
ITEM_NO ITEM_SUBNO
======= =========
1 A
1 B
2 B
3 A
3 B
How can I combine into one table as follow?
ITEM_NO ITEM_SUBNO_1 ITEM_SUBNO_2
======= ============ ===========
1 A B
2 B
3 A B
-
select item_no, item_subno_1 = case when item_subno = 'A' then item_subno end, item_subno2 = case when item_subno = 'B' then item_subno end
-
Maybe my example misunderstanding
I have one master and one child table. Each master record has at most 2 child records. How can I present it as follows:
Master
ITEM_NO ITEM_FLAG
====== ==========
1 Y
2 N
3 Y
Child Table
ITEM_NO ITEM_SUBNO
======= =========
1 A
1 B
2 C
3 D
3 E
How can I combine into one table as follow?
ITEM_NO ITEM_FLAG ITEM_SUBNO_1 ITEM_SUBNO_2
======= ========= ============ ===========
1 Y A B
2 N C
3 Y D E
-
Maybe my example misunderstanding
I have one master and one child table. Each master record has at most 2 child records. How can I present it as follows:
Master
ITEM_NO ITEM_FLAG
====== ==========
1 Y
2 N
3 Y
Child Table
ITEM_NO ITEM_SUBNO
======= =========
1 A
1 B
2 C
3 D
3 E
How can I combine into one table as follow?
ITEM_NO ITEM_FLAG ITEM_SUBNO_1 ITEM_SUBNO_2
======= ========= ============ ===========
1 Y A B
2 N C
3 Y D E
-
Does item_subno contains all alphabet?
-
declare @master table(item_no int, item_flag varchar(1))
declare @child table(item_no int, item_subno varchar(1))
insert into @master values(1, 'Y')
insert into @master values(2, 'N')
insert into @master values(3, 'Y')
insert into @child values(1, 'A')
insert into @child values(1, 'B')
insert into @child values(2, 'C')
insert into @child values(3, 'D')
insert into @child values(3, 'E')
select
item_no
, item_flag
, item_subno1
, item_subno2 =
case
when item_subno1 = item_subno2 then null
else item_subno2
end
from
(
select
m.item_no, m.item_flag
, item_subno1 =
(
select top 1 item_subno
from @child
where item_no = m.item_no
order by item_subno
 
, item_subno2 =
(
select top 1 item_subno
from @child
where item_no = m.item_no
order by item_subno desc
 
from @master m
 
as MyTable -
Oops! Instead of smile picture ')'
-
But I think more powerfull to change insert algorithm. For example, create INSTED OF trigger or create a sp wich do insert or update to child table with additional fileds(item_subno1 and item_subno2)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply