SQL joining from list of values to match single value?

[Bob Sneidar]

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. 

I am not an SQL guru and few and far between are the times I ever actually have to craft a join. If I need relational data, I pull the data into Livecode as arrays and do multiple queries on the chid tables if necessary. I understand this is not efficient for large datasets. 

I think often if I am having a problem developing a query, it is because I haven't created the database structure very well. 

Bob S


> On Sep 8, 2016, at 11:42 , Peter Haworth <pete at lcsql.com> wrote:
> 
> Never a good idea to put lists of items into a single column.  If you need
> that capability, best to define another table with uniqdna column as a
> foreign key to your main table and a column to hold a single pardna, then
> have 1 row for each uniqdna/pardna pair.  With thst structure the query is
> just a matter of a simple join between the two tables.
> 
> On Thu, Sep 8, 2016 at 11:23 AM Mike Kerner <MikeKerner at roadrunner.com>
> wrote:
> 
>> 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."
>> _______________________________________________
>> 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
>> 
> _______________________________________________
> 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