Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    6

    Unanswered: Joining multiple tables

    When I run:

    select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
    scheme.opheadm.customer, qty_received
    from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
    where
    effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

    It gives me the 5 rows that I need to work with, one column is customer (which is giving me customer code) that I want to replace with customer name from another table

    So I tried:
    select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
    scheme.jcmastm.name, qty_received
    from scheme.opheadm
    join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
    join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer

    where
    effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

    this works with the same 5 rows that i need but loops them through every customer from the table scheme.jcmastm giving me a total of 960 rows not just the 5 that i want to work with. Can anyone explain why this is looping?

  2. #2
    Join Date
    Nov 2011
    Posts
    6

    My Fault

    Sorry just saw my issue I should have had

    join scheme.jcmastm on scheme.jcmastm.customer = scheme.opheadm.customer


    not join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer

Tags for this Thread

Posting Permissions

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