I cannot figure this one out. I have a Category table with 50,000 records, an Inventory table with over 2 million records. A Sales table with 500,000 records. And a LineItem table with 800,000 records pairing the Inventory ID with the Sales Transaction ID and Quantity. I need to generate a Quantity sold year to date for a certain vendor. The vendor code can be found in the Category table which has a relationship with Inventory. I am trying a SQL statement like this:
select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID IN (select invID from Inventory where categoryid IN (select categoryid from Category where vendcode='AA')) this yields null when I know there are sales for that vendor in 2005. Simplified schemas for the tables are as follows: Category: +----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------------+------+-----+---------+----------------+ | vendcode | char(3) | YES | MUL | NULL | | | categoryID | int(10) unsigned | | PRI | NULL | auto_increment | +----------------+------------------+------+-----+---------+----------------+ Inventory: +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | categoryID | int(11) | YES | MUL | NULL | | | invID | int(10) | | PRI | 0 | | | itemnum | int(11) | YES | MUL | NULL | | +--------------+---------------+------+-----+---------+-------+ Sales: +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | saletranID | int(10) unsigned | | PRI | NULL | auto_increment | | solddate | datetime | YES | | NULL | | +--------------+------------------+------+-----+---------+----------------+ LineItem: +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | invID | int(10) | YES | MUL | NULL | | | quantity | int(10) | YES | | NULL | | | saletranID | int(10) | YES | MUL | NULL | | +------------+---------+------+-----+---------+-------+ Can anybody shed some light on this and if this is even possible. I have indexes in place and the query is still slow to pull. Thanks a million, Nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
