problem with query

hello and good day!

...this works fine

select a.invce_no, a.invce_slsamt_yen, a.invce_slsamt_php, a.invce_slsamt_dlr, a.crnc_code, a.date_prn,
a.fob_charge,b.fx_amt,b.invce_amt,b.crnc_code,b.ba l_amt,b.amt_php,b.rate_php from delivery_header a left join delivery_trans_detail b on
a.invce_no = b.invce_no where cust_code='200105000011

here is the problem...

i need additional columns,
trans_date and ref_date on another table which is delivery_trans_header
where delivery_trans_header.trans_no = delivery_trans_detail.trans_no

could anyone pls tell me what to do? how could i connect the third table? do i have to use another join? if so, how?Use that, new rows are upper:

select
a.invce_no,
a.invce_slsamt_yen,
a.invce_slsamt_php,
a.invce_slsamt_dlr,
a.crnc_code, a.date_prn,
a.fob_charge,
b.fx_amt,
b.invce_amt,
b.crnc_code,
b.bal_amt,
b.amt_php,
b.rate_php,
C.TRANS_DATE,
C.REF_DATE
from
delivery_header a
left join
delivery_trans_detail b
on a.invce_no = b.invce_no
INNER JOIN -- is join type correct, check
DELIVERY_TRANS_HEADER C
ON C.TRANS_NO = B.TRANS_NO
where
cust_code='200105000011'|||thanks for the quick reply!

it did display the additional columns but there's still one problem, it didnt display the colums with no matches, ive tried inner, left and right but to no avail, all of it has the same output...

wish you could still help me with this|||try with
"full outer join"|||i tried it, but it still has the same output, it still didnt display the columns with no matches...|||Originally posted by BuTcHoK
i tried it, but it still has the same output, it still didnt display the columns with no matches...

TRY DOING A LEFT OUTER JOIN ON A SUBQUERY, SOMETHING LIKE...

select
a.invce_no,
a.invce_slsamt_yen,
a.invce_slsamt_php,
a.invce_slsamt_dlr,
a.crnc_code, a.date_prn,
a.fob_charge,
b_d.fx_amt,
b_d.invce_amt,
b_d.crnc_code,
b_d.bal_amt,
b_d.amt_php,
b_d.rate_php,
b_d.TRANS_DATE,
b_d.REF_DATE
from
delivery_header a
left outer join

(select
b.invce_no,
b.fx_amt,
b.invce_amt,
b.crnc_code,
b.bal_amt,
b.amt_php,
b.rate_php,
c.TRANS_DATE,
c.REF_DATE
FROM delivery_trans_detail b
INNER JOIN DELIVERY_TRANS_HEADER C
ON C.TRANS_NO = B.TRANS_NO) B_D

on b_d.invce_no = a.invce_no
where
a.cust_code='200105000011'|||hi,

it tried it, but it says token unknown "select" ...|||Give an example what exactly want to be matched, pls ..
May be it is miss understanding ...|||Originally posted by BuTcHoK
hi,

it tried it, but it says token unknown "select" ...

Try to remove the blank lines between
a) left outer join AND (select
and
b) TRANS_NO) B_D AND on b_d.invce_no = a.invce_no|||please see attached file for example... thanks so much|||I make test - everything is OK. Use it.

declare @.a table(pk int, pk1 int)
declare @.b table(pk1 int, pk2 int)
declare @.c table(pk2 int, qq int)

insert into @.a values(1,10)
insert into @.a values(2,20)
insert into @.a values(3,30)
insert into @.a values(4,40)

insert into @.b values(10,100)
insert into @.b values(20,200)
insert into @.b values(50,500)

insert into @.c values(100,1000)
insert into @.c values(300,3000)
insert into @.c values(500,5000)

select * from @.a
select * from @.b

select * from @.a a left outer join @.b b on a.pk1 = b.pk1
left outer join @.c c on b.pk2 = c.pk2