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 I need
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:
SELECT x.*
FROM
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
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
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
On 8 Jul 2014, at 9:01pm, Paul Sanderson sandersonforens...@gmail.com 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 command
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
] 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
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
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
-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 unique
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 matching
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
/\ 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] query help
I have a table of the form
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 tried this query.
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 id. Even if
On Sun, 10 Mar 2013 20:36:47 +0530
Navaneeth.K.N navaneet...@gmail.com 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 part
Hello,
Thanks for the help.
On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik i...@tandetnik.org 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
cchu, 20907
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,
-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;
3061626364
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 rog...@rogerbinns.com wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 18/04/12
-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
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
-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 near
On Wed, Apr 18, 2012 at 6:38 PM, Jim Sanders jim.c.sand...@gmail.comwrote:
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:
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
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
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 a
Marco Bambini ma...@sqlabs.net 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 I used a
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 ma...@sqlabs.net wrote:
I have a table Clients defined as (simplified version):
CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT,
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
, Defects.W
,
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
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
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) and (200,
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 this is
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));
Marco Bambini ma...@sqlabs.net 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, prop_key TEXT,
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
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
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 of
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
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
Ian Hardingham i...@omroth.com 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 find the total
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
Ian Hardingham i...@omroth.com 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 it from
Many thanks again Igor.
On 16/11/2010 13:15, Igor Tandetnik wrote:
Ian Hardinghami...@omroth.com 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
...@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 Hardinghami...@omroth.com wrote:
Thanks Igor
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
Paul Sanderson sandersonforens...@gmail.com 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
Thank You.
On 26 October 2010 13:14, Igor Tandetnik itandet...@mvps.org wrote:
Paul Sanderson sandersonforens...@gmail.com 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
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
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
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:
Thanks
,'\','')) || '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-tp29983175p2755
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: Likely SQL
Dickie.wild dickie.w...@yahoo.com 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 strCover TO
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
-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
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
On 19 October 2010 16:26, jeff archer jarch...@yahoo.com 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
10, 3015,
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
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 file
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 on
-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 to another column
, the beauty with it is that it always works regardless of which
characters you have in the filename. I though I'd share it.
select RTRIM(path,REPLACE(path,'\','')) from
(select 'C:\richEminem\file.txt' path)
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls
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
Marco Bambini ma...@sqlabs.net 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 10
6 2 20
7
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
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 unique work,
: 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 {20 minutes ago}
GROUP BY ProcessID, FileName
Unfortunately when a file
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 index
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,
72 matches
Mail list logo
