Odd query results

[Mike Kerner]

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.

On Thu, Dec 15, 2016 at 5:38 PM, Bob Sneidar <bobsneidar at iotecdigital.com>
wrote:

> Good idea, except I think I set the primary key columns to unsigned. Also,
> they have to be unique, so I cannot have more than one unassigned device,
> which would often be the case.
>
> I may have to alter my schema but of course that cuts the total number of
> integers I have available in half. Not a big deal though.
>
> Bob S
>
>
> On Dec 14, 2016, at 18:58 , Mike Kerner <MikeKerner at roadrunner.com<mailto:
> MikeKerner at roadrunner.com>> wrote:
>
> I'd have to see your SQL, but how are you checking for null and empty?
> Null is a funky value in SQL that can mean a variety of things, and an
> empty value in an integer column will give you heartache.  WIth many
> DBMS's, you have to check IS NULL or IS NOT NULL depending on the column
> values and types.  See the syntax reference for the DBMS you are using.  If
> I was in your position, 0 would be unassigned and -1 would be
> decommissioned, the column would be set to NOT NULL, and a trigger would
> set it to 0 if it IS NULL() for insert and for update.
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



-- 
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."