SQL joining from list of values to match single value?

Dr. Hawkins dochawk at gmail.com
Thu Sep 8 13:16:02 EDT 2016


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



More information about the use-livecode mailing list