Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    38

    Unanswered: Left join problem

    Hi!

    I'm having troubles properly executing a left join situation. I either get all rows in the right table or inner join behaviour.

    The database stucture 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 for 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 for example which gives me all-time sales:

    Code:
    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
    I have to admit it's not a SQL Server db, but this is basically a logical query problem unrelated to database type, and I didn't have any luck in the other forum section.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The script contains twice the same alias name
    ...
    from pub.item i
    ...
    left join pub.invoice i
    ...

    Didn't your database system threw an error?

    Try this:
    Code:
    select i.itemcode, 
    	i.itemname, 
    	v.version, 
    	sum(COALESCE(il.price, 0)) as 'sales'
    from pub.item i
    	inner join pub.itemvers v on 
    		i.itemcode = v.itemcode
    	left OUTER join pub.invline il on 
    		i.itemcode = il.itemcode and 
    		v.version = il.version
    	left OUTER join pub.invoice inv on 
    		il.invnum = inv.invnum AND
    		inv.invoicedate between to_date('1/1/2011') and to_date('2/1/2011')
    where i.itemgrp = 'MN2077'
    group by i.itemcode, 
    	i.itemname, 
    	v.version
    I have not tested this query. If it's not giving the correct result, give me the CREATE TABLE scripts and some INSERT scripts to populate the tables with some sample data and I will investigate this further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2010
    Posts
    38
    Thanks for your reply! The Invoice table alias was wrong in my post, sorry, I don't know how that got screwed up.

    There's still the same problem with this query though. I get the same result with and without "AND inv.invoicedate between to_date('1/1/2011') and to_date('2/1/2011'".

    I don't have access to any sort of database manager or scripts, I only have ODBC read access.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This might get you what you want.
    NOTE: I have ??? by ITEMNAME because you don't list that column but you did indicate it was from the ITEM table.
    Code:
    SELECT ITM.ITEMCODE
         , ITM.ITEMNAME
         , INV.VERSION
         , SUM(INV.PRICE) AS SALES
    FROM (SELECT ITEM.ITEMCODE
               , ITEM.ITEMNAME ????
               , VERS.ITEMGRP 
          FROM ITEM
                 INNER JOIN
               ITEMVERSION VERS
                 ON     ITEM.ITEMCODE = VERS.ITEMCODE
                    AND ITEM.ITEMGRP  = 'MN2077'
         ) AS ITM
           LEFT OUTR JOIN
           (SELECT INVL.PRICE
                 , INVL.ITEMCODE
            FROM INVOICE INVO
                   INNER JOIN
                 INVLINE INVL
                   ON     INVO.IVNUM = INVL.IVNUM
                      AND INVO.INVOICEDATE BETWEEN TO_DATE('1/1/2011')
                                               AND TO_DATE('2/1/2011')
           ) AS INV
             ON ITM.ITEMCODE = INV.ITEMCODE
    GROUP BY ITM.ITTEMCODE
           , ITM.ITEMNAME
           , INV.VERSION

  5. #5
    Join Date
    Feb 2010
    Posts
    38
    Yes, that was exactly what I wanted it to do! Thanks a lot Stealth_DBA!

Posting Permissions

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