On Nov 2, 3:51 pm, Mo <goo...@voyagercomponents.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

I think it just clicked, in regards to your original instructions
regarding the INSERT statements.
Once we get the dump completed, I'll just weed out most of the INSERTs
to just keep some base info which applies.