I spent several minutes looking at your question and your data model and nothing jumped out at me that precluded you from determining the quantity of the this vendor's items sold via this data model. I might have missed something though.
I'm a little concerned that your LineItem table appeared to have no primary key. In my opinion, the primary key of a line item table should be an order number (saleTranID?) and then a sequence number (1 for the first item on the order, 2 for the second, etc.) but you (apparently) have no primary key defined at all and don't have a sequence number either. However, that shouldn't keep this particular query from running or returning appropriate rows. I am also assuming that invID is an inventory ID - my brain kept reading it as "invoice ID" but I learned to ignore it ;-) - where an inventory ID uniquely identifies one product that you sell, e.g. invID 1 might be power supplies for Sony camcorders while invID 2 might be Palm Tungsten E PDAs. This is something I would normally call a product ID if I were doing the naming :-) If, in fact, invID *is* an invoice ID, i.e. something that uniquely identifies a particular sales transaction then there is something wrong which might explain why you're not getting any data. So, assuming I haven't misunderstood anything or simply missed something, I would be inclined to break the query down into chunks. Execute each chunk on its own andmake sure that each chunk delivers what you think it should. If it doesn't, either the query is wrong or the data isn't what you think it is. Verify that the data you expect is there by doing SELECTs against the relevant tables; if the data is there, it's got to be your query that is wrong. Inspect each chunk until you find the culprit(s) in either the SQL or the data. Also, for what it's worth, I would strongly suggest that you set up a test environment with a SMALL quantity of data in each table - 50 rows or less should be plenty for most situations - and try your queries against that test environment. That makes the testing process a lot less painful - why wait for many seconds or even minutes for the query to give you the wrong answer? - and let's you solve the problem faster. It might sound like a lot of work but it shouldn't be; just clone the "real" tables and then copy a small but representative sample of data from the real tables into the clones. You also asked about performance but there is no way anyone can comment on that without knowing a lot more about what indexes you have and, perhaps, which engine you are using. But, in my opinion, your first effort should be directed toward getting the query running correctly, THEN worry about making it go faster. Rhino ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Friday, November 04, 2005 12:28 AM Subject: SQL help for qty Sold YTD... > 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] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.12.7/160 - Release Date: 03/11/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
