Re: Quick SQL question

2013-07-15 Thread Klaus major-k
Hi Pete, Am 15.07.2013 um 21:43 schrieb Peter Haworth : > On Mon, Jul 15, 2013 at 10:54 AM, Klaus major-k wrote: > >> Same value in all columns! > > > Hi Klaus, > OK, should probably be OK then. Great, thanks! > There is one corner case but it's pretty > unlikely. > > Let's say there is

Re: Quick SQL question

2013-07-15 Thread Peter Haworth
On Mon, Jul 15, 2013 at 10:54 AM, Klaus major-k wrote: > Same value in all columns! Hi Klaus, OK, should probably be OK then. There is one corner case but it's pretty unlikely. Let's say there is a row with your three columns containing: code sign xyz ...and the value you are looking for i

Re: Quick SQL question

2013-07-15 Thread Klaus major-k
Hi Peter, Am 15.07.2013 um 17:39 schrieb Peter Haworth : > I'm thinking this may not give you the same results as checking each column > individually. Is the need to check for the same value in each column or > for a different value in each column? Same value in all columns! > Pete > lcSQL Sof

Re: Quick SQL question

2013-07-15 Thread Peter Haworth
I'm thinking this may not give you the same results as checking each column individually. Is the need to check for the same value in each column or for a different value in each column? Pete lcSQL Software On Mon, Jul 15, 2013 at 6:39 AM, Klaus major-k wrote: > > Am 15.

Re: Quick SQL question

2013-07-15 Thread Klaus major-k
Am 15.07.2013 um 15:59 schrieb j...@splash21.com: > Googling it suggests that '||' is concatenate in SQLite, so try; > > WHERE (name || tags || comment) LIKE '%. Wonderful, will try this! Thanks a bunch! Best Klaus -- Klaus Major http://www.major-k.de kl...@major-k.de

Re: Quick SQL question

2013-07-15 Thread john
Googling it suggests that '||' is concatenate in SQLite, so try; WHERE (name || tags || comment) LIKE '%. Quoting Klaus major-k : Hi Pete, Am 15.07.2013 um 15:25 schrieb j...@splash21.com: Most engines have a concatenate command, something like; WHERE CONCAT(name, tags, comment) LIKE

Re: Quick SQL question

2013-07-15 Thread Klaus major-k
Hi Pete, Am 15.07.2013 um 15:25 schrieb j...@splash21.com: > Most engines have a concatenate command, something like; > WHERE CONCAT(name, tags, comment) LIKE '%tUserInput%' Aha, cool! Thanks! Do you know if this supported by SQLite? > :) > > Quoting Klaus major-k : > >> Hi firends, >> >> wi

Re: Quick SQL question

2013-07-15 Thread john
Most engines have a concatenate command, something like; WHERE CONCAT(name, tags, comment) LIKE '%tUserInput%' :) Quoting Klaus major-k : Hi firends, with a table consisting of 3 (or more) db fields: name tags comment If I want to find a value in ALL of the three (or more) db fields, I now

Quick SQL question

2013-07-15 Thread Klaus major-k
Hi firends, with a table consisting of 3 (or more) db fields: name tags comment If I want to find a value in ALL of the three (or more) db fields, I now use: ... ## Pseudocode, quotes etc missing, I know! put fld "searchfield" into tUserInput select * from myTable where name LIKE %tUserInput% OR

Re: SQL question

2012-09-19 Thread Kay C Lan
During development a useful couple of lines of code are, assuming you've been through your repeat for each col_name loop to create your query with it's many OR conditions: put tQuery into tTemp replace comma with cr in tTemp replace " OR " with cr & " OR " in tTemp --replace " AND " with cr & " AN

Re: SQL question

2012-09-19 Thread Klaus on-rev
Hi Peter, Am 19.09.2012 um 18:21 schrieb Peter Haworth : > On Wed, Sep 19, 2012 at 3:07 AM, Klaus on-rev wrote: > ... >>> Klaus, have you implemented a solution to this yet? I don't recall you >>> mentioning which flavor of SQL you're using and that makes a difference. >> ah, sorry, I will use a

Re: SQL question

2012-09-19 Thread Bob Sneidar
I wrote a function that did that. You pass it a comma del list of column names, and it builds an sql statement for you. I will try to dig that up somewhere. Bob On Sep 19, 2012, at 9:21 AM, Peter Haworth wrote: > I implemented exactly the same functionality in a name/address search db > once

Re: SQL question

2012-09-19 Thread Peter Haworth
On Wed, Sep 19, 2012 at 3:07 AM, Klaus on-rev wrote: > Hi Peter, > > Am 19.09.2012 um 07:40 schrieb Peter Haworth : > > > I'm not sure that will work. If Column2 started with "MyCriteria", I > don't > > think it would be selected. Any queries of the form LIKE '%mycriteria%' > > would work though

Re: SQL question

2012-09-19 Thread Peter Haworth
Don't know why that would be Mark. I simply reply to messages I get from the list. I guess if the original message was sent to the old and new address then my reply would be too. When sending new messages, I send to use-livecode@lists.runrev.com. Pete lcSQL Software On

Re: SQL question

2012-09-19 Thread Klaus on-rev
Hi Peter, Am 19.09.2012 um 07:40 schrieb Peter Haworth : > I'm not sure that will work. If Column2 started with "MyCriteria", I don't > think it would be selected. Any queries of the form LIKE '%mycriteria%' > would work though. using wildcards is not my problem ;-) > Klaus, have you implemen

Re: SQL question

2012-09-19 Thread Richmond
On 09/19/2012 11:37 AM, Mark Schonewille wrote: Pete, I get all your messages twice. Could you please stop sending copies to the old address of this list? A bit like Sesame Street's "Vincent Twice", nicht? -- Best regards, Mark Schonewille Economy-x-Talk Consulting and Software Engineeri

Re: SQL question

2012-09-19 Thread Mark Schonewille
Pete, I get all your messages twice. Could you please stop sending copies to the old address of this list? -- Best regards, Mark Schonewille Economy-x-Talk Consulting and Software Engineering Homepage: http://economy-x-talk.com Twitter: http://twitter.com/xtalkprogrammer KvK: 50277553 Use Col

Re: SQL question

2012-09-19 Thread AndyP
never has bugs. It just develops random features. PointandSee is a FREE simple but full featured under cursor colour picker / finder. http://www.pointandsee.co.uk - made with LiveCode (v1.4.1 released 26/08/2011) -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/SQ

Re: SQL question

2012-09-18 Thread Peter Haworth
I'm not sure that will work. If Column2 started with "MyCriteria", I don't think it would be selected. Any queries of the form LIKE '%mycriteria%' would work though. Klaus, have you implemented a solution to this yet? I don't recall you mentioning which flavor of SQL you're using and that makes

SQL question

2012-09-18 Thread Mark Stuart
on Mon Sep 17 10:59:49 CDT 2012, Klaus on-rev wrote: I am wondering if it is possible to search the complete table instead of a particular column. Know what I mean? Like this pseudocode: ... SELECT * FROM Persons WHERE ANY_COLUMN LIKE 'sa%' ... Thanks for any hints or workarounds if this is not pos

Re: SQL question

2012-09-17 Thread Bob Sneidar
Peters way seems like it would be quicker, although more work to implement, because it keeps an index of the columns you want to search for up to date. For a large table this would be the way to go. For a smaller dataset, you could probably get away with Mike's solution of using the concat() fun

Re: SQL question

2012-09-17 Thread Klaus on-rev
Hi Pete, Am 17.09.2012 um 18:41 schrieb Peter Haworth : > Hi Klaus, > ... > > SELECT * FROM Index WHERE Table='Persons AND Text LIKE 'sa%' JOIN Persons > on Person.=Index.Key > > If you are using SQLite, your could use Triggers to keep the Index Table up > to date with any inserts, updates, or

Re: SQL question

2012-09-17 Thread Klaus on-rev
Hi Mike, Am 17.09.2012 um 18:38 schrieb Mike Bonner : > Might look here. > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103033 Theres an > example for locating an exact match that is simple, another > example that uses like, but not sure you'd want to mess with it. (seems to > be a convoluted

Re: SQL question

2012-09-17 Thread Peter Haworth
Hi Klaus, Don't think you cand o that with standard SQL tables. You can do it with SQLite Full TExt Search tables It could also be done with standard tables as follows. Set up an index table with columns named table, text, and key. In your example, every time your Persons table is updated, you w

Re: SQL question

2012-09-17 Thread Mike Bonner
Might look here. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103033 Theres an example for locating an exact match that is simple, another example that uses like, but not sure you'd want to mess with it. (seems to be a convoluted way to use 'or') I also found this, --select * from contacts whe

Re: SQL question

2012-09-17 Thread Klaus on-rev
Hi Mark, Am 17.09.2012 um 18:25 schrieb Mark Schonewille : > Hi Klaus, > > The easy way to do this is to retrieve all column names, use a script (PHP or > LiveCode) to create the syntax for searching all columns with OR statements > and use another statement to retrieve the data with your now

Re: SQL question

2012-09-17 Thread Mark Schonewille
Hi Klaus, The easy way to do this is to retrieve all column names, use a script (PHP or LiveCode) to create the syntax for searching all columns with OR statements and use another statement to retrieve the data with your now dynamic SQL syntax. MySQL has no built-in way to do this. -- Best reg

SQL question

2012-09-17 Thread Klaus on-rev
Hi friends, I know that we can use WILDCARDS in database searches with "LIKE" ... SELECT * FROM Persons WHERE City LIKE 'sa%' ... I am wondering if it is possible to search the complete table instead of a particular column. Know what I mean? Like this pseudocode: ... SELECT * FROM Persons WHERE