Need help with complex SQL statement 1

[johncook]

I have an app that maintains multiple inventories (perhaps better to call it multiple product listings).

I have multiple facilities. Each facility has it's own set of products that are sold.

I have created a Products table with it's own maintanance capabilities ("Product Master Maintenance").

To keep the user from having to maintain a separate products table for every product in every facility, I have a "Product Exceptions Maintenance" function. Most products and products information(fields), are the same for all facilities but often things like price are different per facility.

So the way this works is that when a new product is entered to the product master, it applies to all facilities unless there is an exception for that product, in the exceptions table.

I probably overkilled that explanation!

Anyway, I want to write a view that results in one row per product, with the exception, if any, overwriting the master where applicable. The view would of course only contain one facilities data. Currently I am wtriting the master to a cursor then scan...endscan that cursor and finding, if any, an exception and replacing the master data with the exception data. I just want to simplify this if possible.

My tables: Products, ProdPerFac
The tables are exactly alike except ProdPerFac contains cfacilities_id which relates to Facilities.cid

Any assistance appreciated,
John

 

[craigsboyd]

John,

Try doing a UNION...it will give you distinct records just make sure to do the exceptions table first and make sure that the fields are the same by name/type/size...
I'm assuming that ProdPerFac is your exceptions table...

Select blah, blah2, cFacilities_id as blah3 from ProdPerFac ;
Union ;
Select blah, blah2, cid as blah3 from Products ;
into cursor crsTemp

[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[craigsboyd]

I noticed after I hit submit that you say that the records will not be necessarily exact which would lead me to believe you may have to add partially to the solution I proposed...in the second portion of the UNION query you may need to use a WHERE NOT IN clause such as...


Select blah, blah2, cFacilities_id as blah3 from ProdPerFac ;
Union ;
Select blah, blah2, cid as blah3 from Products ;
WHERE cid NOT IN (select cFacilities_id from ProdPerFac) ;
into cursor crsTemp

....whatever your conditions are for the first half of the union query they should be duplicated in the Where's subquery.

[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[johncook]

Thanks Craig,
You gave me hope. I have almost got it after a few hours of working at it.

There is more to it because users are able to discontinue a product at the master level or at the facility level and some more issues such as the whole idea of the facility level is to override the master level values, so the exception rows are never the same as the master rows therefore VFP's union isn't combining the master and exception rows. Anyway, as I said, I have resolved most of this.

I will post when completed.
Thanks again,
John

 

[craigsboyd]

Thanks for the star. If you end up needing a little further help post the fieldnames and some sample data from both tables, and then what your hoped for result would be and then what you have so far for an SQL statement, and I'm sure myself or another member could help you polish it off. If not, then good luck and I will look forward to the end result.

[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]