[sqlite] Query help

Sorry, i was too fast. Correction SELECT engine,coalesce(groupname,'*') as groupname,databasename,key,value FROM EnginePreferences left join groups on (groups.groupid = EnginePreferences.groupid); OR SELECT engine,groupname,databasename,key,value FROM EnginePreferences left join

[sqlite] Query help

Hi, SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value FROM EnginePreferences left join groups on (groups.groupid = EnginePreferences.groupid); OR SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value FROM EnginePreferences left join (select

[sqlite] Query help

I have a table EnginePreference: CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, UNIQUE(engine,databasename,key)) and a table Groups: CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname

Re: [sqlite] Query help

> There are two ways to rewrite this query, with a correlated subquery: > > SELECT * > FROM table_a AS x > WHERE NOT EXISTS (SELECT 1 >FROM table_b AS y >WHERE x.id = y.id > AND x.col = y.col) > > or with an outer join: > >

Re: [sqlite] Query help

Joseph L. Casale wrote: > SELECT x.id, x.col > FROM table_a x > EXCEPT > SELECT y.id, y.col > FROM table_b y > JOIN table_a . This query is not complete, but as far as I can tell, it is intended to return table_a rows that do not have a matching table_b row. Is this correct? > now

[sqlite] Query help

I have a query I am trying to rewrite as efficient as possible and not clear. SELECT x.id, x.col FROM table_a x EXCEPT SELECT y.id, y.col FROM table_b y JOIN table_a . The right hand part of the except performs several joins and already duplicates the entire query on the left hand

Re: [sqlite] Query help

> On 8 Jul 2014, at 9:01pm, Paul Sanderson wrote: > > 0, microsoft, mac > 1, oracle, mac > 2, oracle, pc SELECT t1.recno, t2.name, t3.name FROM t1 JOIN t2 ON t2.a = t1.a JOIN t3 ON t3.b = t1.b ORDER BY t1.recno If you have lots of data on your tables this

Re: [sqlite] Query help

On 7/8/2014 4:01 PM, Paul Sanderson wrote: i have three tables create table t1 (recno int, a int, b int) create table t2 (a int, name text) create table t3 (b int, name text) I want to create a query that lists all rows in t1 but rather than the integers a and b I want to display the

[sqlite] Query help

I suspect this is easy i have three tables create table t1 (recno int, a int, b int) create table t2 (a int, name text) create table t3 (b int, name text) I want to create a query that lists all rows in t1 but rather than the integers a and b I want to display the associated names from t2 and

Re: [sqlite] Query help

> Will the SQL 1969 "EXCEPT" compound operator not work for some reason? Worked perfect, my sql is weak as I didn't even know of this one... Thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Query help

ers@sqlite.org' > Subject: [sqlite] Query help > > Hey guys, > I am trying to left join the results of two selects that both look > exactly like this: > > SELECT DISTINCT SUBSTR(col, INSTR(col, 'string')) AS name FROM > table_a > > Both tables have the exact data type

[sqlite] Query help

Hey guys, I am trying to left join the results of two selects that both look exactly like this: SELECT DISTINCT SUBSTR(col, INSTR(col, 'string')) AS name FROM table_a Both tables have the exact data type and format, I need to reformat each tables results, then join and return only what is in

Re: [sqlite] query help

tml e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Paul Sanderson > Sent: Monday, 20 May, 2013 08:00 > To: General Discussion of SQLite Database > Subject: [sqlite] qu

Re: [sqlite] query help

On 5/20/2013 7:59 AM, Paul Sanderson wrote: I have a table of the form create table tab (num int1 unique, num2, int) for each row for num2 there is usually a matching num1. But not always. I want to identify each row where num2 does not have a matching num1 select * from tab where num2 not

Re: [sqlite] query help

On Mon, May 20, 2013 at 12:59:45PM +0100, Paul Sanderson scratched on the wall: > I have a table of the form > > create table tab (num int1 unique, num2, int) > > for each row for num2 there is usually a matching num1. But not always. > > I want to identify each row where num2 does not have a

Re: [sqlite] query help

); 5 7 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Monday, May 20, 2013 7:00 AM To: General Discussion of SQLite Database Subject: [sqlite] query help I have a table of the form create table tab (num int1

[sqlite] query help

I have a table of the form create table tab (num int1 unique, num2, int) for each row for num2 there is usually a matching num1. But not always. I want to identify each row where num2 does not have a matching num1 example data might be num1 num2 1 3 2 3 3 2 4

Re: [sqlite] Query Help

Hello, Thanks for the help. On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik wrote: > On 3/10/2013 11:06 AM, Navaneeth.K.N wrote: >> >> select distinct(lower(pattern)) as pattern, id from symbols where >> value1 = ?1 or value2 = ?1 group by pattern >> >> This returns >>

Re: [sqlite] Query Help

On Sun, 10 Mar 2013 20:36:47 +0530 "Navaneeth.K.N" wrote: > select distinct(lower(pattern)) as pattern, id from symbols where > value1 = ?1 or value2 = ?1 group by pattern Igor's answer is correct. I just want to point out what looks like a misunderstanding on your

Re: [sqlite] Query Help

On 3/10/2013 11:06 AM, Navaneeth.K.N wrote: select distinct(lower(pattern)) as pattern, id from symbols where value1 = ?1 or value2 = ?1 group by pattern This returns "cchu", "20907" "chchu", "20879" "chu", "20935" This is distinct set of patterns, but I am not getting the list ordered by

[sqlite] Query Help

Hi Guys, I have a table named "symbols". I am writing the below query. select lower(pattern), id from symbols where value1 = ?1 or value2 = ?1 This returned the following results. "chu", "20851" "chchu", "20879" "cchu", "20907" "chu", "20935" >From this, I need only distinct patterns. So I

Re: [sqlite] query help with inline conversion of hex data

On Wed, Apr 18, 2012 at 6:38 PM, Jim Sanders wrote: > > It's disappointing there isn't a simple "unhex()" function to do the > reverse of the already existing function. > Code to implement such a function is contained in the SQLite test suite. See it here:

Re: [sqlite] query help with inline conversion of hex data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/04/12 15:38, Jim Sanders wrote: > It's disappointing there isn't a simple "unhex()" function to do the > reverse of the already existing function. That would be a very > elegant solution to this issue, as I am so close. unhex isn't anywhere

Re: [sqlite] query help with inline conversion of hex data

Well yes I agree the stored format isn't ideal, but unfortunately I have no control over that. These are hard drive serial numbers and that is the format that Microsoft WMI spits them out in. It's possible that the Spiceworks application could be modified to decode those hex strings it gets

Re: [sqlite] query help with inline conversion of hex data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/04/12 14:39, Jim Sanders wrote: > I found that, but I need essentially the reverse of that. The data > stored in SQL is 3061626364 and I need to convert it back to 0abcd > (from your example) The correct fix is to get them to stop storing

Re: [sqlite] query help with inline conversion of hex data

I found that, but I need essentially the reverse of that. The data stored in SQL is 3061626364 and I need to convert it back to 0abcd (from your example) On Wed, Apr 18, 2012 at 5:35 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On

Re: [sqlite] query help with inline conversion of hex data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/04/12 14:30, Jim Sanders wrote: > But I can't figure out a way do this dynamically for all rows. There is a builtin hex() function. sqlite> create table foo(bar); insert into foo values('0abcd'); sqlite> select hex(bar) from foo;

[sqlite] query help with inline conversion of hex data

I am trying to write a query to a SQLite database (Spiceworks) in which I need to transform some data as part of the SELECT statement. The query I *want* to use looks like this (includes some pseudo code): SELECT d.name, d.current_user, d.device_type, d.model, d.operating_system,

Re: [sqlite] Query help

Thanks a lot Igor, it's perfect now. -- Marco Bambini http://www.sqlabs.com On Jun 12, 2011, at 4:46 PM, Igor Tandetnik wrote: > Marco Bambini wrote: >> I have a table Clients defined as (simplified version): >> CREATE TABLE Clients (id INTEGER PRIMARY KEY

Re: [sqlite] Query help

Marco Bambini wrote: > I have a table Clients defined as (simplified version): > CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity > TEXT, ping_timeout INTEGR); > > ping_timeout was a global property so in order to get a list of all clients > timedout

Re: [sqlite] Query help

No I am sorry but I need to query the ping_timeout column from inside the same query. -- Marco Bambini http://www.sqlabs.com On Jun 12, 2011, at 2:02 PM, Roger Andersson wrote: > On 06/12/11 01:52 PM, Marco Bambini wrote: >> things are recently changed in my app and ping_timeout is now

Re: [sqlite] Query help

On 06/12/11 01:52 PM, Marco Bambini wrote: > things are recently changed in my app and ping_timeout is now a client > property set inside the Clients table (and no longer a global property), so I > would like to perform the query: > snprintf(sql, sizeof(sql), "select id from Clients where

[sqlite] Query help

Hello guys, I have a table Clients defined as (simplified version): CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity TEXT, ping_timeout INTEGR); each time a client performs an operation the last_activity column is updated with: UPDATE Clients SET

Re: [sqlite] Query help

On 1 Mar 2011, at 22:01, Jeff Archer wrote: > Hi all, and thanks in advance for you help. > > And this select which I would like to modify to only return lowest X,Y value > for each ImageID. > > select Defects.DefectID > , Defects.ImageID > , Defects.AnalysisID > , Defects.X > , Defects.Y > ,

Re: [sqlite] Query help

On 3/1/2011 6:47 PM, Jeff Archer wrote: > I think it will just happen to work out if I could get the first row > for each ImageID since the values should have been entered in > ascending order. I realize this will probably not be guaranteed to > get lowest X,Y but for my purpose at the moment

Re: [sqlite] Query help

>From: Igor Tandetnik [mailto:itandet...@mvps.org] >Sent: Tuesday, March 01, 2011 5:47 PM > >On 3/1/2011 5:01 PM, Jeff Archer wrote: >> And this select which I would like to modify to only return lowest X,Y >> value for each ImageID. > >What does "lowest" mean? If you have two points (100, 200)

Re: [sqlite] Query help

On 3/1/2011 5:01 PM, Jeff Archer wrote: > And this select which I would like to modify to only return lowest X,Y value > for each ImageID. What does "lowest" mean? If you have two points (100, 200) and (200, 100), which one is "lower"? -- Igor Tandetnik

[sqlite] Query help

Hi all, and thanks in advance for you help. I have the following schema CREATE TABLE [Scans] (ScanIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Timestamp DATETIME NOT NULL UNIQUE ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,ResultVARCHAR ); CREATE

Re: [sqlite] Query help

Thanks Igor and thanks Martin, I need to add both the id and the other properties to an hash table (a Cocoa NSDictionary) so I needed a way to have a key, value representation that includes also the id. I solved the problem with 2 queries and some Cocoa code. I don't like complex queries and 2

Re: [sqlite] Query help

On 2/2/2011 11:16 AM, Marco Bambini wrote: > your query returns 3 columns, but I need just two columns (key, value for > example). Why? You have all the information you need, just in a slightly different (and, arguably, easier to use) form. > The first row should be the label 'ID' and the id

Re: [sqlite] Query help

Hello Igor, your query returns 3 columns, but I need just two columns (key, value for example). The first row should be the label 'ID' and the id of the MKObjects followed by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id. For example MKObjects contains (1,IPHONE,PANEL,0) and

Re: [sqlite] Query help

Hello Marco, As far as i can see, the union is necessary. However, the second select in the union can be rewritten as a join: SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION SELECT prop_key, prop_value FROM MKProperties JOIN MKObjects on

Re: [sqlite] Query help

Marco Bambini wrote: > Hello, I have two tables defined as: > > CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, > type TEXT, parent_id INTEGER DEFAULT 0); > CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id > INTEGER,

[sqlite] Query help

Hello, I have two tables defined as: CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, type TEXT, parent_id INTEGER DEFAULT 0); CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key));

Re: [sqlite] Query help - two similar queries

s-boun...@sqlite.org on behalf of Ian Hardingham Sent: Tue 11/16/2010 7:31 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Query help - two similar queries Many thanks again Igor. On 16/11/2010 13:15, Igor Tandetnik wrote: > Ian Hardingham<i...@omroth.com> w

Re: [sqlite] Query help - two similar queries

Many thanks again Igor. On 16/11/2010 13:15, Igor Tandetnik wrote: > Ian Hardingham wrote: >> Thanks Igor. Can i get custom results like >> >> GamesWonByPlayer1 >> >> By using getColumn in the normal way? > I'm not familiar with the term "custom result". GamesWonByPlayer1 is

Re: [sqlite] Query help - two similar queries

Ian Hardingham wrote: > Thanks Igor. Can i get custom results like > > GamesWonByPlayer1 > > By using getColumn in the normal way? I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a column alias which a) is completely optional, you could safely drop

Re: [sqlite] Query help - two similar queries

Thanks Igor. Can i get custom results like GamesWonByPlayer1 By using getColumn in the normal way? That may be a stupid question - I guess what I mean is, are those custom identifiers treated as column names when reading back from the select? Thanks, Ian On 16/11/2010 13:04, Igor Tandetnik

Re: [sqlite] Query help - two similar queries

Ian Hardingham wrote: > I have a badly designed structure for a table which records /games > played/ by people. It looks like: > > id > player1 > player2 > score > > If score > 0, player 1 won the game. If score < 0, player 2 won it. > (Score of 0 is a draw). > > I wish to

[sqlite] Query help - two similar queries

Hey guys. I have a badly designed structure for a table which records /games played/ by people. It looks like: id player1 player2 score If score > 0, player 1 won the game. If score < 0, player 2 won it. (Score of 0 is a draw). I wish to find the total record in games between two specific

Re: [sqlite] query help

Thank You. On 26 October 2010 13:14, Igor Tandetnik wrote: > Paul Sanderson wrote: >> I have two tables, table b is a subset of table a. both tables have >> the same primary key >> >> I want to update the rows from table a with a single column

Re: [sqlite] query help

Paul Sanderson wrote: > I have two tables, table b is a subset of table a. both tables have > the same primary key > > I want to update the rows from table a with a single column from table > b, what sql command would be most efficient for this? update a set

[sqlite] query help

I have two tables, table b is a subset of table a. both tables have the same primary key I want to update the rows from table a with a single column from table b, what sql command would be most efficient for this? Thanks ___ sqlite-users mailing list

Re: [sqlite] Query help

On 19 October 2010 16:26, jeff archer wrote: > I have a table containing width and height of images with columns wPixels, > hPixels.  I would like to select all rows that have either a unique wPixels > or a > unique hPixels value. > > for this data: > 10, 20 > 10, 20 > 10, 30

[sqlite] Query help

I have a table containing width and height of images with columns wPixels, hPixels.  I would like to select all rows that have either a unique wPixels or a unique hPixels value. for this data: 10, 20 10, 20 10, 30 10, 3015, 10 15, 30 15, 30 15, 30 I would like to select: 10, 20 10, 30 15, 10

Re: [sqlite] EXTERNAL: SQLite query help pls

-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls Hi, Well i have to say i am like a kid in a sweet shop right now, you all may have just saved me 6 or so hours work. Thanks again for your input. I was wondering if anyone had any issues with me posting this up

Re: [sqlite] EXTERNAL: SQLite query help pls

people that it came from here. Thanks again, Rich -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users

Re: [sqlite] EXTERNAL: SQLite query help pls

Dickie.wild wrote: > I thought that looked like it would get the same results, but i seem to be > getting the following error, are you able to try it and let me know if your > getting the same error? > > SQLiteManager: Likely SQL syntax error: UPDATE video_files SET

Re: [sqlite] EXTERNAL:Re: EXTERNAL: SQLite query help pls

2010 7:35 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls Hi, I thought that looked like it would get the same results, but i seem to be getting the following error, are you able to try it and let me know if your getting the same error? SQLiteManager

Re: [sqlite] EXTERNAL: SQLite query help pls

,'\','')) || 'folder.jpg') [ near "TO": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp2998317

Re: [sqlite] EXTERNAL: SQLite query help pls

From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin Sent: Tue 10/19/2010 7:17 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls On 19 Oct 2010, at 1:07pm, Dickie.wild wrote: > Tha

Re: [sqlite] EXTERNAL: SQLite query help pls

On 19 Oct 2010, at 1:07pm, Dickie.wild wrote: > Thanks for the reply's i have tried the various ways described that they do > not seem to be working. I have a way in which it works but this is in SQL > could anyone convert it to SQLite for me? i am not to sure it is even > possible. I have also

Re: [sqlite] EXTERNAL: SQLite query help pls

it for me? Update video_files Set strCover = Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) + 'Folder.jpg' http://old.nabble.com/file/p2497/Boxee_catalog.db Boxee_catalog.db -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p2497

Re: [sqlite] EXTERNAL: SQLite query help pls

(select 'C:\richEminem\file.txt' path) -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p29985874.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: SQLite query help pls

10/17/2010 7:26 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] SQLite query help pls Hi All, I was hoping for a little help, well little would be an understatement, I currently have a file location in a field and i would like to take all of it up untill the last / (folder) and copy it in

Re: [sqlite] SQLite query help pls

On 17 Oct 2010, at 1:26pm, Dickie.wild wrote: > I was hoping for a little help, well little would be an understatement, I > currently have a file location in a field and i would like to take all of it > up untill the last / (folder) and copy it in to another column and then > attach folder.jpg

Re: [sqlite] SQLite query help pls

You want to strip a complete path + name and save them as separate fields, or you already have it splitted and want to join them together ? On 10/17/2010 09:26 AM, Dickie.wild wrote: > > Hi All, > > I was hoping for a little help, well little would be an understatement, I > currently have a

[sqlite] SQLite query help pls

something like c:\rich\Eminem\folder.jpg I have never used SQLite before and help would be great R -- View this message in context: http://old.nabble.com/SQLite-query-help-pls-tp29983175p29983175.html Sent from the SQLite mailing list archive at Nabble.com

Re: [sqlite] Query help

"Marco Bambini" wrote in message news:3265458b-af7b-434f-83e8-f9448bab0...@sqlabs.net > Hello all, > > I have a table foo (id INTEGER, tid INTEGER, optype INTEGER), > and I have some data into foo: > id id2 optype > - > 1 2 10 > 2 2 10 > 3 2 10 > 4 2 10 > 5 2

[sqlite] Query help

Hello all, I have a table foo (id INTEGER, tid INTEGER, optype INTEGER), and I have some data into foo: id id2 optype - 1 2 10 2 2 10 3 2 10 4 2 10 5 2 10 6 2 20 7 2 10 8 2

Re: [sqlite] Query help?

What if you took a slightly different tack? CREATE TABLE FinishedWork ( EventTime INTEGER NOT NULL, FileName TEXT NOT NULL, ProcessID INTEGER NOT NULL, BytesProcessed INTEGER NOT NULL, isDuplicate integer-- tri-state: 0=not duplicate 1=duplicate null=unknown ); And then periodically run

Re: [sqlite] Query help?

"Doug" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Thank you Igor. The GROUP BY was the secret (I was trying to do a > GROUP BY on the outer select, but it wasn't quite cutting it). > > GROUP BY is very powerful, but I notice it has a performance cost. > Is there a way to use an

Re: [sqlite] Query help?

Tandetnik > Sent: Tuesday, May 27, 2008 12:24 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Query help? > > Doug <[EMAIL PROTECTED]> wrote: > > SELECT ProcessID, count(1), sum(BytesProcessed) > > FROM FinishedWork > > WHERE EventTime >

Re: [sqlite] Query help?

Doug <[EMAIL PROTECTED]> wrote: > SELECT ProcessID, count(1), sum(BytesProcessed) > FROM FinishedWork > WHERE EventTime > {20 minutes ago} > GROUP BY ProcessID, FileName > > Unfortunately when a file is processed twice, it's counted twice (ie > added into the sum twice) and I need to show only

[sqlite] Query Help

Hi, I Have a problem with the following query executed on sqlite SELECT CUSTOMER, PRODUCT, [RANK] FROM (SELECT CUSTOMER, PRODUCT, [SUM_SALES], (SELECT COUNT(T2.SUM_SALES) FROM (SELECT CUSTOMER,