Never a good idea to put lists of items into a single column. If you need that capability, best to define another table with uniqdna column as a foreign key to your main table and a column to hold a single pardna, then have 1 row for each uniqdna/pardna pair. With thst structure the query is just a matter of a simple join between the two tables.
On Thu, Sep 8, 2016 at 11:23 AM Mike Kerner <mikeker...@roadrunner.com> wrote: > I would make IS IN work, and do it in two queries, OR, in the case where > you have something less than 10k values, just build a container and chunk > it. If that wasn't an option, I'd N:N it, and just add the extra tables > and a join. > > On Thu, Sep 8, 2016 at 1:16 PM, Dr. Hawkins <doch...@gmail.com> wrote: > > > I've been trying to wrap my head around this query for weeks, and hope > > someone can point out what I'm missing. > > > > In my table, the main key is uniqDna, an integer. There can also be > > dpdnDnas and parDnas, the uniqDna of a dependent or parent dna. > > > > I'm not seeking any good way to keep the dndDnas and parDnas fields other > > than as space departed lists. > > > > I'm trying to find a sane way to SELECT upon values contained in one of > > those lists. (if there could only be one value, this would be trivial). > > > > The closest I'm seeing is making sure that there are spaces before and > > after the list, so uniqDna 2 might have dpdDnas " 6 7 8 ", and then > "SELECT > > uniqDna, stuff FROM theTable WHERE parDnas LIKE '% 2 %'"--but this has to > > be done one by one. > > > > What I'm looking for is something like > > > > "SELECT uniqDna, stuff, parDnas FROM theTABLE WHERE > > some_other_entry.parDnas CONTAINS uniqDna" > > > > That is, a list of all the uniqDna that have parents, with there > associated > > parDnas > > > > As i understand it, IS IN () needs a list of literals, rather than > another > > query result. > > > > I'm looking at tables with dozens, not thousands, of entries. Hundreds > > *might* be conceivable as a rare case, but isn't a concern for general > use. > > > > I'm assuming that some kind of JOIN might do this, but the problem still > > remains of the operator to use to check for an integer value in one > column > > being one of the words in another . . . > > -- > > Dr. Richard E. Hawkins, Esq. > > (702) 508-8462 > > _______________________________________________ > > use-livecode mailing list > > use-livecode@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." > _______________________________________________ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
