Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: 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?

  2. #2
    Join Date
    Oct 2003
    Location
    Sofia
    Posts
    16
    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'

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    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

  4. #4
    Join Date
    Oct 2003
    Location
    Sofia
    Posts
    16
    try with
    "full outer join"

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    i tried it, but it still has the same output, it still didnt display the columns with no matches...

  6. #6
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    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'

  7. #7
    Join Date
    Oct 2003
    Posts
    5
    hi,

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

  8. #8
    Join Date
    Oct 2003
    Location
    Sofia
    Posts
    16
    Give an example what exactly want to be matched, pls ..
    May be it is miss understanding ...

  9. #9
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    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

  10. #10
    Join Date
    Oct 2003
    Posts
    5
    please see attached file for example... thanks so much
    Attached Files Attached Files

  11. #11
    Join Date
    Oct 2003
    Location
    Sofia
    Posts
    16

    Wink

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •