SQL joining from list of values to match single value?

[Dr. Hawkins]

On Thu, Sep 8, 2016 at 11:22 AM, Mike Kerner <MikeKerner at roadrunner.com>
wrote:

> I would make IS IN work, and do it in two queries,
>

Something like

put revDataFromQuery(tab,vtab, theDb,"SELECT uniqDna, parDnas FROM theTable
WHERE (parDnas <>'');" into theData

split theData by vtab and tab



or would


> OR, in the case where
> you have something less than 10k values, just build a container and chunk
> it.
>

SELECT p.uniqDna  , d.uniqDna, d.parDnas d.stuff FROM theTable d INNER
JOING theTable p
     ON p.uniqDna IS IN (d.parDnas)


Or can I even use IS IN in a join?

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:10 PM, Ben Rubinstein <benr_mc at cogapp.com> wrote:


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


Mathematically, I think this increases the complexity by a t least one, and
possibly two orders . . .

In :memory:, sqlite has two main tables for each "debtor".  One, the dinfo
table, has a few entries per row (key, value, default value, and a couple
of others), and has a few hundred entities.  The second has the debts and
assets (dna), with typically a couple of dozen to a few dozen rows, but
with close to 100 different columns of data.  Both tables have columns to
indicate change.

Periodically, the changed elements are stored in the postres database
(local or remote), and any changed data there comes back.  In fact, the
data just written comes right back.  In the "remote" database, I simply
store the INSERT statements to recreate the entry in memory.

I use almost no relational features (perhaps none); this turned out to be a
perfect solution to synchronizing multiple users working on the same file.
The most recent change (by the postgres clock) governs (for my application,
if conflicting values were to be entered, the attorney would be dealing
with far more serious problems than software can deal with).

I suppose there could be a third table, but I'd need a clean way to update
the remote.  (The uniq identifiers are actually created there, with only a
temporary id until write/writeback).  I *think* it could be done in roughly
the same way, but I haven't hashed it out.


On Thu, Sep 8, 2016 at 3:19 PM, Bob Sneidar <bobsneidar at iotecdigital.com>
 wrote:

> I beg to differ. Sometimes lists are the best way to do it. I have a
> column that can contain any combination of 3 values: customer, site, and IT
> or nothing at all. I use vert bars as delimiter, so my query is ex. "...
> where contacttype LIKE '%|site|%'". I also have another column where a
> contact can be linked to several different sites. So "... where sited LIKE
> '%|1546|%'". This is a MUCH simpler way of managing a list of possible
> values without using lookup tables and joins.
>

This is sort of what I"m thinking.  And maybe commas for the separators, to
feed straight to IS IN if that is allowed (I don't have addresses, just
integers).

Do you include a delimiter after your first and last entries?  It seems
clumsy, but I don't see a way around it with LIKE
-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462