Odd query results

[Bob Sneidar]

Thanks Mike. You are correct in your assessment. Many sites can belong to a customer, many devices to a site. But of course each primary key is unique. The user is required to find a customer, after which they are presented with a list of sites for that customer, and selecting one in a datagrid presents them with the devices at that site along with contacts, service records, notes etc. It kind of cascades.

The problem I was having is that when doing a query for an empty custom, it would find the one with a custid of 0, and yesterday when I set the custid to -1, it found that one too! I don't have to deal with NULL so much because I have a function called denullify which replaces all NULL values in an array with empty strings.

What I will have to do is conditionally check for empty values and set the dgdata of the customer data grid to empty instead of populating it. Or else I can simply not have a "warehouse inventory" customer record or site record and have an alternate means of finding decommissioned devices.

Thanks for your help.

Bob S


On Dec 15, 2016, at 18:04 , Mike Kerner <MikeKerner at roadrunner.com<mailto:MikeKerner at roadrunner.com>> wrote:

They should be unique in the one table, not the many table.  So if I
understand what you're doing, you should have a table for locations, with
the id (unique), and the name.  Then in the devices table they will also
have an id, which is unique, but then it will also have a locationid, which
is not unique as it references the id in the locations table.  If you are
using unsigned for the location, then you will have to check IS NOT NULL()
or IS NULL() and use null for the cases where you haven't assigned a
location, yet (e.g. new records), and you could use 0 for out-of-service.