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 <p...@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 <mikeker...@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 <doch...@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
