I could swear I've done this type of thing before and am sure I'm
overlooking something simple.
Is this correct syntax?
SELECT im.invoice_date as invoice_date,im.pay_by as
due_date,im.invoice_id as invoice_id, im.invoice_number as
invoice_number,im.invoice_date as created,im.status as status,
Johnstone, Simon wrote:
>
> $query = "SELECT a.company, b.company FROM tblusers a inner join
> tblcompany b on (a.company = b.company) ";
>
> This works but yet again doesn't print any results to the screen.
>
Simon,
Try the following to see if you have any trailing spaces in your table
the current release of sqlite, as does the "inner join"
syntax.
- Jeff
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Join troub
2008 16:17
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Join trouble
Simon,
If the query is returning no rows, my guess would be trailing spaces in
the data. This has been discussed at length and there is a new collating
sequence (http://www.sqlite.org/cvstrac/chngview?cn=4732)
Did you try with an inner join with table aliases?
so:
from tblusers a inner join tblcompany b on
(a.company = b.company)
RBS
> I am having serious trouble creating a join on my database. I have
> tested it in other databases and it works so it must be a problem with
> sqlite and the join??? I
(tblcompany.company)
- Jeff
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:11 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Join trouble
I am having serious trouble creating a join on my database. I have
P Kishor <[EMAIL PROTECTED]> wrote:
> sqlite> select * from node limit 3;
> node_id node_type node_name node_confidence
> -- -- - ---
> 1 ACT Mike Garrison 0.99932
> 2 ACT Pat Cox0.999193
> 3
Hello,
I am having trouble grasping the following issue intuitively --
sqlite> select * from node limit 3;
node_id node_type node_name node_confidence
-- -- - ---
1 ACT Mike Garrison 0.99932
2 ACT Pat Cox
On Wed, 2 Jan 2008 17:49:36 -0600, "Jay Sprenkle"
<[EMAIL PROTECTED]> wrote:
>On Jan 2, 2008 5:31 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>
>I found a solution that seems workable. I ended up rewriting my query
>class so it assumes a bunch of things which are valid for my application
>(but not
On Jan 2, 2008 5:31 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> >If the engine within sqlite recorded the row id as it
> >fetched each column it would be trivial and would
> >prevent me from having to query the database again.
> >If it's not present in the current code
I found a solution that
On Tue, 1 Jan 2008 18:56:25 -0600, "Jay Sprenkle"
<[EMAIL PROTECTED]> wrote:
>> I fail to see why you can't simply include the PRIMARY KEY
>> (portable) or ROWID (not portable) in your SELECT statement if
>> you apparently need them later.
>
>I could, but that would involve changing a lot of
On Tue, 1 Jan 2008 17:16:45 -0600, "Jay Sprenkle"
<[EMAIL PROTECTED]> wrote:
>Almost. If the join isn't done using the primary key then knowing the
>primary key isn't helpful.
You told us you needed that information to know which row and
column to update (edit). You already know the column.
To
On Jan 1, 2008 4:58 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> You know the query, and also the schema, for example from
> PRAGMA table_info(tabelname);
> That includes primary key info.
> Merge that with the info from the resultset and you know what
> you need to know, don't you?
Almost.
On Tue, 1 Jan 2008 16:40:21 -0600, "Jay Sprenkle"
<[EMAIL PROTECTED]> wrote:
>> >If they try to edit a column from the Role table how do I get to Role.Id?
>> >If I can get Sqlite to tell me what it used to select the row in the result
>> >set
>> >it would be much easier that the alternatives.
>>
> >If they try to edit a column from the Role table how do I get to Role.Id?
> >If I can get Sqlite to tell me what it used to select the row in the result
> >set
> >it would be much easier that the alternatives.
>
> You can always query the ROWIDs (as long as there is no GROUP
> BY), with
On Tue, 1 Jan 2008 15:53:05 -0600, "Jay Sprenkle"
<[EMAIL PROTECTED]> wrote:
>Good afternoon all,
>
>I'm working on a project using Sqlite. I'm using a query to fill a
>table presented to the user.
>I'd like to be able to edit the data cell by cell. I can easily call
>the column metadata
Good afternoon all,
I'm working on a project using Sqlite. I'm using a query to fill a
table presented to the user.
I'd like to be able to edit the data cell by cell. I can easily call
the column metadata
functions and get the database, table, and column where that data came from.
If I want to
See the ATTACH statement.
Shilpa Sheoran wrote:
Does sqlite allow joining tables in different database files using
triggers or any other mechanism? Does it affect the performance?
Thanks
Shilpa
-
To unsubscribe,
Thank very very much
That is the information that i was searching for!
;)
On 12/04/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
Cesar Rodas wrote:
> Thanks for the answer mister Hipp, but I am searching an SQL help.
> I'd like
> to know how is the SQLite join algorithm or whe
Cesar Rodas wrote:
Thanks for the answer mister Hipp, but I am searching an SQL help.
I'd like
to know how is the SQLite join algorithm or where i could find it (in
what
".c" file).
Thank for the help! ;)
Cesar,
You should check out the slide show at
http://www.sqlite.org/ph
On 12/04/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
"Cesar Rodas" <[EMAIL PROTECTED]> wrote:
> Hello.
>
> I have a question about SQLite join-mechanisms. Let me explain with an
> example.
>
> I have the follow table.
> CREATE TABLE a
"Cesar Rodas" <[EMAIL PROTECTED]> wrote:
> Hello.
>
> I have a question about SQLite join-mechanisms. Let me explain with an
> example.
>
> I have the follow table.
> CREATE TABLE a(
>word_id INTEGER,
>doc_id INTEGER
> );
>
I want to know SQLite join algorithm
On 12/04/07, P Kishor <[EMAIL PROTECTED]> wrote:
On 4/12/07, Cesar Rodas <[EMAIL PROTECTED]> wrote:
> Hello.
>
> I have a question about SQLite join-mechanisms. Let me explain with an
> example.
>
> I have the follow table.
On 4/12/07, Cesar Rodas <[EMAIL PROTECTED]> wrote:
Hello.
I have a question about SQLite join-mechanisms. Let me explain with an
example.
I have the follow table.
CREATE TABLE a(
word_id INTEGER,
doc_id INTEGER
);
CREATE INDEX "a_index1" ON "a"(
"doc_id&
Hello.
I have a question about SQLite join-mechanisms. Let me explain with an
example.
I have the follow table.
CREATE TABLE a(
word_id INTEGER,
doc_id INTEGER
);
CREATE INDEX "a_index1" ON "a"(
"doc_id" ASC
);
CREATE INDEX "a_index" ON a (
On 7/7/06, Roger <[EMAIL PROTECTED]> wrote:
Can anyone help me out here, i am a bit stuck and this is a very strange
error indeed!
Having a copy of your database might help if you can post it.
On 7/7/06, Roger <[EMAIL PROTECTED]> wrote:
I have the following query
CREATE VIEW MyStuff AS
SELECT Com . CompanyID CompID , PublicationID PubID ,
Your SQL looks very confused.
Are there really spaces around the period here or is that just in
this message? Spaces are important for the
I have the following query
CREATE VIEW MyStuff AS
SELECT Com . CompanyID CompID , PublicationID PubID ,
RelationshipDescription Relationship , CompanyName Name , BranchName
Branch , BranchID
FROM Publication_Branch_Relationships AS PBRel
LEFT JOIN ltblPublication_Branch_Relationship AS BRel ON
=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote:
> > I downloaded your database and the query above was indeed slow.
> > But then I ran "ANALYZE" so that SQLite can gather statistics
> > on the various indices, then reran the query. This time, SQLite
> > was able to use the ANALYZE
=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote:
>
> create table Media (id integer, key, value, source integer);
> and the indexes:
> create unique index key_idx on Media (id,key,source);
> create index prop_idx on Media (key,value);
> create index source_idx on Media (key,source);
I downloaded your database and the query above was indeed slow.
But then I ran "ANALYZE" so that SQLite can gather statistics
on the various indices, then reran the query. This time, SQLite
was able to use the ANALYZE results to make better index choices
and the query is quite speedy.
The
Friday, March 24, 2006, 2:33:36 PM, Tobias Rundström wrote:
> [...]
> The schema is this:
> create table Media (id integer, key, value, source integer);
> and the indexes:
> create unique index key_idx on Media (id,key,source);
> create index prop_idx on Media (key,value);
> create index
=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote:
>
> create table Media (id integer, key, value, source integer);
> and the indexes:
> create unique index key_idx on Media (id,key,source);
> create index prop_idx on Media (key,value);
> create index source_idx on Media (key,source);
El 24-03-2006, a las 16:08, [EMAIL PROTECTED] escribió:
Elcin Recebli <[EMAIL PROTECTED]> wrote:
Hi.
You're joining the table with itself using 'id'. However, there's
no index just on that field. I'm not sure how exactly SQLite
utilises indices, but it might be unable to use index on
Elcin Recebli <[EMAIL PROTECTED]> wrote:
> Hi.
>
> You're joining the table with itself using 'id'. However, there's no index
> just on that field. I'm not sure how exactly SQLite utilises indices, but it
> might be unable to use index on (id,key,source) triple to optimise
> calculation of
Hi.
You're joining the table with itself using 'id'. However, there's no index just
on that field. I'm not sure how exactly SQLite utilises indices, but it might
be unable to use index on (id,key,source) triple to optimise calculation of
"m1.id = m2.id".
Does this sound sensible?
Cheers.
Hello,
I guess this subject is a bit worn out. But I am having scalabillity
problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle
insanely huge media libraries, playlists and clients. Our goal is to
be able to run medialibs with 50.000 files without problem. Our
backend is
Thank you. That answers my question.
Awesome Sqlite!
Darren Duncan wrote:
At 9:49 PM -0400 1/28/05, [EMAIL PROTECTED] wrote:
Hi All,
I wonder does Sqlite have feature such as join two string fields
together.
e.g: Select FirstName + LastName From Username
Thank you,
Ming
The '+' is a numerical
At 9:49 PM -0400 1/28/05, [EMAIL PROTECTED] wrote:
Hi All,
I wonder does Sqlite have feature such as join two string fields together.
e.g: Select FirstName + LastName From Username
Thank you,
Ming
The '+' is a numerical addition; its result is the sum of 2 numbers;
that is not what you want.
Hi All,
I wonder does Sqlite have feature such as join two string fields together.
e.g: Select FirstName + LastName From Username
Thank you,
Ming
Loading shared libraries is easy enough on windows and
Linux, but throw AIX and HPUX and Solaris into the mix
and suddenly things become more complicated. SQLite is
a cross-platform library. It has to work on more than
just windows.
Really? Both these platforms support shared libraries - I use
> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 02, 2004 4:49 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Join function in select statement
>
>
> CARIOTOGLOU MIKE wrote:
> > Why not provide some extra
CARIOTOGLOU MIKE wrote:
Why not provide some extra functionality on one platform ?
Because that's not what SQLite does. SQLite works the same
on all platforms.
Note also that were this policy to change and SQLite were
allowed to have extra features on selected platforms, it is
most likely that
On Tue, Nov 02, 2004 at 04:36:41PM +0200, CARIOTOGLOU MIKE wrote:
> [user-defined functions loadable through shared libraries]
> true. OTOH, since, as you say, it *is* simple, why not provide some extra
> functionality on one platform ? it does not affect other platforms, and it
> is s nice to
mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 02, 2004 3:44 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Join function in select statement
>
>
> CARIOTOGLOU MIKE wrote:
> > write a user-defined function in your hosting code. the
> only problem is,
CARIOTOGLOU MIKE wrote:
write a user-defined function in your hosting code. the only problem is,
people that use your database (as opposed to your code), will not be able to
use the function. DRH, what about DLL-loadable functions ? this should be
easy to arrange, and would be of enormous help.
Marcel Strittmatter wrote:
Hi all
I like to have a join function that I can use in a select statement like
this:
select join(name, ',') from people;
this should produce something like this:
scott,martin,jones,adams
It is not possible to use perl or another script language. The only
language I
Really appreciate your help! However that query doesn't give correct
results (though it does give a row for every customer!!!)..
The problem is the sum() in the join isn't qualified against the
selected customer ID..
Using this :
SELECT c.customer_number as customer_number
> select customers.*, ctots.total
> from customers,
> (select customer_id cid, sum(invoice_amount) total
>from invoice_master group by cid) ctots
> where customers.customer_id = ctots.cid
You're right -- sorry, I wan't paying attention.
For 'customers, (subquery) where'
Very nice, however that still only gives me the customer records if they
have an invoice in the invoice_master table..
A sub-select or outer join or something of the sort is needed but I
can't get it to work..
In PostgreSQL I might do :
SELECT *,(SELECT sum(total) FROM invoice_master WHERE
> ...get all customers records, plus the sum of a column in the invoice...
The idea is to get customer_id and the sum from the invoice table,
then join that with the rest of the customer info. Of course, if
you want to do it in one SQL statement, you have to write those
steps in reverse order:
So I'm sitting here in a pinch and my brain just refuses to work...
2 tables, a customer and an invoice table. What is the proper SQL to get
all customers records, plus the sum of a column in the invoice table
with a relation on the customer ID, but not all customers might have an
invoice
> Any Ideas what could be going wrong here.
Hint: '1' is not equal to ' 1'
Regards
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
sqlite> select statustext from statustext, spmblock where spmblock.spmstatus
= statustext.statusid;
sqlite> .schema statustext
CREATE TABLE [StatusText] (
[StatusId] char(4) NOT NULL ,
[StatusText] [nvarchar] (50) NOT NULL
);
sqlite> .schema spmblock
CREATE TABLE SPMBlock (
101 - 154 of 154 matches
Mail list logo
