This may be one of those unhelpful responses which suggests doing something
different instead of answering your question, so apologies in advance - I
don't know how to do the thing you're trying to do.
But if you have control of the database structure, could you re-engineer to do
use a separate table to represent parent child relationships? This is really
exactly what relational databases are designed to do, after all.
That is, instead of (or as well as, if you need it for some other reason)
having dpdnDnas and parDnas as columns in your main table, have a separate
table with those columns.
Then your query is something like
SELECT uniqDna, stuff, parDnas FROM theTABLE, relatTable WHERE
relatTable.parDnas = theTABLE.uniDna
HTH, and apologies if it's something you've already rejected,
Ben
On 08/09/2016 18:16, Dr. Hawkins 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 . . .
_______________________________________________
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
