SQL joining from list of values to match single value?

[Peter Haworth]

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 <MikeKerner at 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 <dochawk at 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 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."
> _______________________________________________
> 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
>