Hi!
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:
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
Any help is greatly appreciated!
dBforums
>
Problem with left join
08-25-11, 09:12
Linear Mode
