Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010

    Unanswered: Problem with left join


    I have this problem with left joining a couple of tables. My database structure is this:

    Table "Item":
    ItemCode, ItemGrp

    Table "ItemVersion":
    ItemCode, Version

    Table "InvLine":
    InvNum, ItemCode, Version, Price

    Table "Invoice":
    InvNum, InvoiceDate

    I want to select all items in a specific item group and sum the price and group by item and version for a specific time period. The time period limiter is InvoiceDate in table Invoice. I want to left join the Price column so that items with no sales are also listed. I've tried a few different queries but I can't quite figure out. I either get only the items with sales or total sales without regard to the invoice date.

    I've tried this one for example, but it selects all sales:
    select i.itemcode, i.itemname, v.version, sum(price) as 'sales'
    from pub.item i
    inner join pub.itemvers v on i.itemcode=v.itemcode
    left join pub.invline il on i.itemcode=il.itemcode and v.version=il.version
    left join (select invnum from pub.invoice where invoicedate between to_date('1/1/2011') and to_date('2/1/2011')) as d on il.invnum=d.invnum
    left join pub.invoice i on d.invnum=i.invnum
    where i.itemgrp = 'MN2077'
    group by i.itemcode, i.itemname, v.version
    Any help is greatly appreciated!

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Without knowing what you've covered in class or at least seeing the full text of the assignment, there isn't much chance that we'll solve the right problem. There are one or more critical pieces of the requirements missing, so the solution is still ambiguous (there is more than one right answer to the question the way that you've posed it).

    The most efficient way to answer this question is to ask your teacher or TA for help. If you want us to help you with it, you need to post the full assignment and as much background information about your class (the book, the current topics you're discussing in class, etc) as possible.

    We won't do your assignment for you, but I'd bet that several of us would be glad to help if we can!

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2010
    Um..thanks for your concern, but this is not an assignment. It's an actual report I'm doing at work, and I think the question is pretty straight forward. I'm guessing you replied to the wrong thread.

  4. #4
    Join Date
    Feb 2010
    It's a Progress database by the way...

Posting Permissions

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