Matt,
Just a gut reaction, but I really do not like the idea of "If there is no product in the table then the store is still valid to receive the product"....
This sort of logic has always bitten me in the past. Unless you have very tight controls over the maintenance of this file, all it takes is for a user to add an entry for a store, and suddenly they cannot order the products they once had access to.
I'm a beliver in special values.... Logically, it is much cleaner to REQUIRE an entry for each store, but if the store is allowed all products, then the entry would read "Store#, *ALL". This entry literally describes what products are available to the store... Nothing should be implied, IMO...
Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Tyler, Matt
Sent: Monday, September 08, 2008 4:05 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL for distinction between not valid and not there
We have a table that records stores that are valid to receive a product.
If a product exists and the store exists then the store is valid to
receive that product. If there is no product in the table then the store
is still valid to receive the product. If the product exists and the
store does not then obviously the store cannot receive the product.
I can tell if the store is valid to receive the product if there is a
store record; however I am having trouble telling the difference between
the store not being valid because there is no product code or not being
valid because it does receive a product code. In testing the last two
cases I always get a NULL result set.
For the sample data provided assume there is another table controlling
the product numbers and there are no gaps. So for #1 (not in sample
data set) store 1 would be valid to receive, but for #8 or 11 store 1
would not be valid to receive product.
Thanks, Matt
Below is the link to SQL code to create table in question with sample
data for table.
http://code.midrange.com/8c8ef57c0f.html
midrange.com
