SQL joining from list of values to match single value?

[Ben Rubinstein]

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