select 1 as transid, 111 as codeper, 1 as empid, 1 as itemcode union all
	select 2 as transid, 112 as codeper, 2 as empid, 2 as itemcode union all
	select 3 as transid, 113 as codeper, 3 as empid, 1 as itemcode union all
	select 4 as transid, 111 as codeper, 1 as empid, 1 as itemcode union all
	select 5 as transid, 112 as codeper, 2 as empid, 2 as itemcode union all
	select 6 as transid, 113 as codeper, 3 as empid, 1 as itemcode union all
	select 7 as transid, 111 as codeper, 1 as empid, 1 as itemcode union all
	select 8 as transid, 113 as codeper, 3 as empid, 1 as itemcode union all	
	select 9 as transid, 111 as codeper, 1 as empid, 1 as itemcode 	

	select 1 as itemcode, 5 as qty union all
	select 2 as itemcode, 50 as qty 

select 1 as transid, 111 as codeper, 1 as empid, 1 as itemcode, 5 as qty union all
select 4 as transid, 111 as codeper, 1 as empid, 1 as itemcode, 0 as qty union all
select 7 as transid, 111 as codeper, 1 as empid, 1 as itemcode, 0 as qty union all
select 9 as transid, 111 as codeper, 1 as empid, 1 as itemcode, 0 as qty union all
select 2 as transid, 112 as codeper, 2 as empid, 2 as itemcode, 50 as qty union all
select 5 as transid, 112 as codeper, 2 as empid, 2 as itemcode, 0 as qty union all
select 3 as transid, 113 as codeper, 3 as empid, 1 as itemcode, 5 as qty union all
select 6 as transid, 113 as codeper, 3 as empid, 1 as itemcode, 0 as qty union all
select 8 as transid, 113 as codeper, 3 as empid, 1 as itemcode, 0 as qty 

quote:

---

Originally posted by sodeep

Select tbd.transid,tbd.codeper,tbd.empid
,tbd.itemcode,coalesce(tbh.qty,0)as qty
from tbldetails tbd left join
tblheaders tbh on tbd.itemcode=tbh.itemcode

---

quote:

---

Originally posted by visakh16

select d.transid,d.codeper,d.empid,d.itemcode,case when d1.itemcode is not null then h.qty else 0 end
from tblDetails d
inner join tblHeader h
on h.itemcode=d.itemcode
left join (select codeper,itemcode,min(transid) as mintran
            from tblDetails
            group by codeper,itemcode)d1
on d1.codeper=d.codeper
and d1.itemcode=d.itemcode
and d1.mintran=d.transid
order by codeper,transid

---

quote:

---

Originally posted by jonasalbert20

quote:

---

Originally posted by visakh16

select d.transid,d.codeper,d.empid,d.itemcode,case when d1.itemcode is not null then h.qty else 0 end
from tblDetails d
inner join tblHeader h
on h.itemcode=d.itemcode
left join (select codeper,itemcode,min(transid) as mintran
            from tblDetails
            group by codeper,itemcode)d1
on d1.codeper=d.codeper
and d1.itemcode=d.itemcode
and d1.mintran=d.transid
order by codeper,transid

---

thanks a lot.




For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

---