SQL joining from list of values to match single value?

[Mike Kerner]

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."