On Nov 2, 12:59 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Mo wrote:
> > On Nov 2, 2:00 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> >> On 2 Nov, 00:03, goo...@voyagercomponents.com wrote:
>
> >>> Or, at least it seems complex to me.
> >>> I need help on my "Dead Stock" report. It seemed pretty simple, but
> >>> I'm discovering it is MUCH more complex than I had anticipated.
> >>> I'm not quite sure which info you would and wouldn't want, so I'm
> >>> just going to lay it ALL out.
>
> >>> We need to list all Stocklines which are older than 01/01/2004, and
> >>> under Part Numbers which have neither been Quoted nor Sold after
> >>> 01/01/2000.
>
> >>> This involves these Tables.Fields:
>
> >>> Part.PartId
> >>> Part.Number
>
> >>> Stockline.PartId
> >>> Stockline.StockId
> >>> Stockline.DateEntered
> >>> Stockline.Location
> >>> Stockline.Quantity
> >>> Stockline.Manufacturer
> >>> Stockline.DateCode
> >>> Stockline.ConsignmentCode
>
> >>> Quote.QuoteId
> >>> Quote.Date
>
> >>> QuoteItem.QuoteId
> >>> QuoteItem.PartId
>
> >>> SalesOrder.SalesOrderId
> >>> SalesOrder.Date
>
> >>> SOItem.SalesOrderId
> >>> SOItem.PartId
>
> >>> This is what I have so far:
>
> >>> SELECT Stockline.PartId, Stockline.StockId, Stockline.PartId,
> >>> Stockline.DateEntered, Stockline.Location, Stockline.Quantity,
> >>> Stockline.Manufacturer, Stockline.DateCode,
> >>> Stockline.ConsignmentCode FROM Stockline
> >>> LEFT JOIN SOItem
> >>> ON Stockline.PartId=SOItem.PartID
> >>> LEFT JOIN QuoteItem
> >>> ON Stockline.PartId=QuoteItem.PartID
> >>> WHERE SOItem.PartID is NULL
> >>> AND QuoteItem.PartID is NULL
> >>> AND Stockline.DateEntered<2004-01-01
>
> >>> This completes nicley, without any errors, but I can't figure out
> >>> how to finish it off.
> >>> I need to include the Part.Number which coincides to each
> >>> Stockline.PartId, and limit it to items where the SalesOrder.Date
> >>> and Quote.Date are either <2000-01-01 or NULL
>
> >>> PLEASE HELP.
> >>> -Mo
>
> >>> Additionally, I want to make sure that the LEFT JOIN's are achieving
> >>> thier intended purposes.
> >>> Would the second LEFT JOIN join to the results of the first JEFT
> >>> JOIN, or the Stockline table?
>
> >> What would be really helpful is some phpMyAdmin exports of the table
> >> schemas and a few items of data in the associated INSERT statement
> >> accompanied by the expected results from that data.- Hide quoted
> >> text -
>
> >> - Show quoted text -
>
> > Forgive my nievity, but I never looked into common terminology.
> > By 'table schemas', do you mean you want all the fields, and a few
> > records for each table?
> > And, please clarify what you are loking for in relation to the INSERT
> > statement. I don't have one in my query, so I'm not sure what you
> > need.
> > -Mo
>
> If you g into phpMyAdmin, as I mentioned in my post, there is an Export tab.
>
> That allows you to export the structure and data. The structure comes in the
> form of CREATE TABLE statements and the data comes in the form of INSERT
> statements. That allows me (or others) to quickly setup tables just like
> your ones and have the sample data in there too.- Hide quoted text -
>
> - Show quoted text -

OK, I did that, but after more than 5,400 lines (which is only the
first of 65 tables), I got an error:
#1146 - Table 'voyager1_voyager.AGENTS' doesn't exist.

This is a table which we aren't really using (it was placed there for
our data-migration into MySQL), but I see it in our tables list.
Any suggestions?

Additionally, if we get the whole dump, it will have a bunch of info
in there which I'm sure our company wants to keep as proprietary.
How should we solve this?

Thanks for your help and patience.