Re: [sqlite] SQL question
Hello Simon, From: Simon Slavin slav...@bigfraud.org On 14 Dec 2009, at 8:52am, Florian Schricker wrote: Primary keys are Oper, Product, Category, Name and CreateTS There is only one primary key per table. So to say the primary key is Oper, Product, Category, Name, CreateTS. No. Go read a boos on databases. The shorter your primary key is, the faster everything works. You want a very short primary key. There would be no point in putting 'CreateTS' in your primary key unless it's possible for two records to exist which have the same Oper, Product, Category, Name but different CreateTS. I might guess that your primary key is probably just Product. If it's not possible to have two records with the same 'Product' then that is your primary key. Please excuse me for being so blunt: You have no idea what I'm supposed to do here nor do you have any idea for whatever historic reason the database or table is designed as is here nor (and finally) do you have no idea about my education on databases or SQL or SQLite in general or in detail so please(!) stop a) implying I have no idea whatsoever on what I'm doing b) suggesting me to go read a book. These actions of yours are rude, not helping in any way and imply I'm a fool in what I'm doing and a fool to ask. On a more constructive side-note: you are completely wrong in your assumption that my primary key is just Product. It isn't. Has never been and will never be. The primary key is as described simply just because it's possible for two records to exist which have the same Oper, Product, Category, Name but different CreateTS. regards, Florian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
On 12/15/09 13:16 , Florian Schricker fschric...@gmail.com wrote: Please excuse me for being so blunt: You have no idea what I'm supposed to do here nor do you have any idea for whatever historic reason the database or table is designed as is here nor (and finally) do you have no idea about my education on databases or SQL or SQLite in general or in detail so please(!) stop a) implying I have no idea whatsoever on what I'm doing b) suggesting me to go read a book. These actions of yours are rude, not helping in any way and imply I'm a fool in what I'm doing and a fool to ask. Well, if the kind people on this list took the time to answer a question *you* asked while having no idea what you're supposed to do, maybe the wrong is with *you*. What kind of business have you asking questions here and then going after people because they don't have any idea what *you* are supposed to do? Why didn't it cross *your* mind that maybe *you* could have provided the necessary background for *your* question? Why didn't it cross *your* mind that if people give *you* suggestions to read a beginners book, then maybe it's because *your* question didn't demonstrate any knowledge that may let them infer otherwise? So please come down from your high horses. People here are a lot more knowledgeable than I am, and as far as I can see from *your* prose alone, than you as well. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Florian Schricker wrote: Hello Simon, SNIP Please excuse me for being so blunt: You have no idea what I'm supposed to do here nor do you have any idea for whatever historic reason the database or table is designed as is here nor (and finally) do you have no idea about my education on databases or SQL or SQLite in general or in detail so please(!) stop I hate to chime in here, but: I'm a starter on SQL / SQLite... Your exact words from your first posting. So either: a. You are a beginner in both SQL and SQLite, in which case the suggestion to obtain the extensive knowledge available from books is valid or b. your statement mislead the group by misinforming them as to your state of knowledge. FWIW John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
On Tue, Dec 15, 2009 at 6:16 AM, Florian Schricker fschric...@gmail.com wrote: Hello Simon, From: Simon Slavin slav...@bigfraud.org On 14 Dec 2009, at 8:52am, Florian Schricker wrote: Primary keys are Oper, Product, Category, Name and CreateTS There is only one primary key per table. So to say the primary key is Oper, Product, Category, Name, CreateTS. No. Go read a boos on databases. The shorter your primary key is, the faster everything works. You want a very short primary key. There would be no point in putting 'CreateTS' in your primary key unless it's possible for two records to exist which have the same Oper, Product, Category, Name but different CreateTS. I might guess that your primary key is probably just Product. If it's not possible to have two records with the same 'Product' then that is your primary key. Please excuse me for being so blunt: You have no idea what I'm supposed to do here nor do you have any idea for whatever historic reason the database or table is designed as is here nor (and finally) do you have no idea about my education on databases or SQL or SQLite in general or in detail so please(!) stop You did say in your original post that you are, a starter on SQL / SQLite and there is some problem (you would) solve in software but (you) have the feeling this can be done using a query. If somebody can help (you) out (you would) be glad - (you) have the feeling there is something to learn for (you) here. Here is the thing 1: Simon, or anyone's advice on this list, is free. The most you should do with it if you disagree with it, unless the person is being downright abusive, is to ignore it. You will lose nothing. Simon's advice, on the other hand, was actually very good. You would do well to heed it. Thing 2: Your schema, even if we know nothing about your problem, demonstrates issues that could bite you in the butt later on. First, instead of having a composite PK made of 5 columns, you would really be better off adding a new INTEGER PRIMARY KEY. Life will become easier on all fronts. Thing 3: If we really have no idea what your situation/problem is, perhaps you should educate us so we can answer you better. If you ask us something, but hide/obfuscate part of the problem, then it wastes everyone's time. This is a nice list. Let's keep it that way please. a) implying I have no idea whatsoever on what I'm doing b) suggesting me to go read a book. These actions of yours are rude, not helping in any way and imply I'm a fool in what I'm doing and a fool to ask. On a more constructive side-note: you are completely wrong in your assumption that my primary key is just Product. It isn't. Has never been and will never be. The primary key is as described simply just because it's possible for two records to exist which have the same Oper, Product, Category, Name but different CreateTS. regards, Florian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, Wisconsin, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question
Hi everyone! I'm a starter on SQL / SQLite and there is some problem I'd solve in software but I have the feeling this can be done using a query. If somebody can help me out I'd be glad - I have the feeling there is something to learn for me here. Here goes: Schema of DB (simplified): - Oper (string) - Product (string) - Category (string) - Name (string) - CreateTS (Timestamp) - Value (Double) Primary keys are Oper, Product, Category, Name and CreateTS Basic usage is dumping measurements as doubles into the table. This happens usually in larger groups like - op, cat, product, m1, 1.234, 2009-11-24 09:49:20.25 - op, cat, product, m2, 1.234, 2009-11-24 09:49:20.25 - op, cat, product, m3, 1.234, 2009-11-24 09:49:20.25 - op, cat, product, m4, 1.234, 2009-11-24 09:49:20.25 Now suppose this has happend a lot so that m1 to m4 is present quite often but of course with differing timestamps in CreateTS (it's a primary key after all). What I could not figure out: Suppose I want to select rows by op, cat, product and get all measurements m1 to m4 with the latest timestamp in CreateTS. So for a set of measurements identified by everything but their name select the latest rows as defined by the timestamp. Can I do that in SQL in one query? Any help is very much appreciated! (I know this might be a simple SQL question for most here and I should better get some training on all that. But if somebody can help a bit with that I'd sure be glad!) kind regards Florian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
So for a set of measurements identified by everything but their name select the latest rows as defined by the timestamp. Can I do that in SQL in one query? Maybe this is what you want: select Name, Value, CreateTS from TableName where Oper = 'op' and Category = 'cat' and Product = 'product' order by CreateTS desc limit 10; Constant in the limit clause depends on how many latest rows do you need. Pavel On Fri, Dec 11, 2009 at 8:13 AM, Florian Schricker fschric...@gmail.com wrote: Hi everyone! I'm a starter on SQL / SQLite and there is some problem I'd solve in software but I have the feeling this can be done using a query. If somebody can help me out I'd be glad - I have the feeling there is something to learn for me here. Here goes: Schema of DB (simplified): - Oper (string) - Product (string) - Category (string) - Name (string) - CreateTS (Timestamp) - Value (Double) Primary keys are Oper, Product, Category, Name and CreateTS Basic usage is dumping measurements as doubles into the table. This happens usually in larger groups like - op, cat, product, m1, 1.234, 2009-11-24 09:49:20.25 - op, cat, product, m2, 1.234, 2009-11-24 09:49:20.25 - op, cat, product, m3, 1.234, 2009-11-24 09:49:20.25 - op, cat, product, m4, 1.234, 2009-11-24 09:49:20.25 Now suppose this has happend a lot so that m1 to m4 is present quite often but of course with differing timestamps in CreateTS (it's a primary key after all). What I could not figure out: Suppose I want to select rows by op, cat, product and get all measurements m1 to m4 with the latest timestamp in CreateTS. So for a set of measurements identified by everything but their name select the latest rows as defined by the timestamp. Can I do that in SQL in one query? Any help is very much appreciated! (I know this might be a simple SQL question for most here and I should better get some training on all that. But if somebody can help a bit with that I'd sure be glad!) kind regards Florian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
On Fri, 11 Dec 2009, Florian Schricker wrote: Schema of DB (simplified): - Oper (string) - Product (string) - Category (string) - Name (string) - CreateTS (Timestamp) - Value (Double) Florian, The schema refers to the set of tables, and the attributes within each table. Is the above one table or a set of tables. Primary keys are Oper, Product, Category, Name and CreateTS There is only one primary key per table. Can I do that in SQL in one query? I urge you to learn about database design and SQL. You can find a lot of information on the Web and there are many good books on each topic. Only one of the above attributes is specified as 'Double', yet you write about dumping data as doubles into each one. Step back and do some learning before you dig a big hole and fall into it. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Florian Schricker fschric...@gmail.com wrote: Schema of DB (simplified): - Oper (string) - Product (string) - Category (string) - Name (string) - CreateTS (Timestamp) - Value (Double) Primary keys are Oper, Product, Category, Name and CreateTS What I could not figure out: Suppose I want to select rows by op, cat, product and get all measurements m1 to m4 with the latest timestamp in CreateTS. So for a set of measurements identified by everything but their name select the latest rows as defined by the timestamp. select * from mytable t1 where oper=:oper and product=:product and category=:category and createTs = (select max(createTs) from mytable t2 where t1.oper = t2.oper and t1.product = t2.product and t1.category = t2.category and t1.name = t2.name ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
On 11 Dec 2009, at 1:13pm, Florian Schricker wrote: - CreateTS (Timestamp) No such type in SQLite. Take a look at http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions http://www.sqlite.org/lang_datefunc.html I'd recommend you store your time/date data either as strings (which would be a column type of TEXT) or as Unix epochs (which would be a column type of INTEGER). Either way, all you need to be sure of is that the field sorts in a consistent manner (i.e. that INDEX or ORDER BY on it will work). Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
If I understand your question correctly, Florian, you want the most
recent Value for each entity represented by the composite primary key
{oper, product, category, name}.
To find the rows that contain the most recent values (although not yet
the values themselves) you first need to aggregate by the composite key
and then use an aggregate max() function to find the latest (biggest)
value for the timestamp column for each aggregation:
select oper, product, category, name, max(CreateTS) as LatestTimestamp
from yourTable
group by oper, product, category, name
The query above returns the rows that contain the most recent
measurement but does not return the value itself. To get your values,
you can join your table again to the query above, representing the query
above as an inline view ( a set of data or a relation):
select oper, product, category, name, value
from yourTable as T
JOIN
(
select oper, product, category, name, max(CreateTS)
from yourTable
group by oper, product, category, name
) as MostRecent
on T.oper = MostRecent.oper
and T.product=MostRecent.product
and T.category=MostRecent.category
and T.name=MostRecent.name
and T.CreateTS = MostRecent.LatestTimeStamp
order by T.oper, T.product, T.category, T.name
NOTE, that this approach assumes the data in column CreateTS is a string
and always follows the format:
2009-11-24 09:49:20.25
-MM-DD HR:MN:SECONDS.HUNDREDTHS
Regards
Tim Romano
Florian Schricker wrote:
Hi everyone!
I'm a starter on SQL / SQLite and there is some problem I'd solve in
software but I have the feeling this can be done using a query. If
somebody can help me out I'd be glad - I have the feeling there is
something to learn for me here.
Here goes:
Schema of DB (simplified):
- Oper (string)
- Product (string)
- Category (string)
- Name (string)
- CreateTS (Timestamp)
- Value (Double)
Primary keys are Oper, Product, Category, Name and CreateTS
Basic usage is dumping measurements as doubles into the table. This
happens usually in larger groups like
- op, cat, product, m1, 1.234, 2009-11-24 09:49:20.25
- op, cat, product, m2, 1.234, 2009-11-24 09:49:20.25
- op, cat, product, m3, 1.234, 2009-11-24 09:49:20.25
- op, cat, product, m4, 1.234, 2009-11-24 09:49:20.25
Now suppose this has happend a lot so that m1 to m4 is present quite
often but of course with differing timestamps in CreateTS (it's a
primary key after all).
What I could not figure out: Suppose I want to select rows by op,
cat, product and get all measurements m1 to m4 with the latest
timestamp in CreateTS.
So for a set of measurements identified by everything but their name
select the latest rows as defined by the timestamp.
Can I do that in SQL in one query?
Any help is very much appreciated! (I know this might be a simple SQL
question for most here and I should better get some training on all
that. But if somebody can help a bit with that I'd sure be glad!)
kind regards
Florian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.426 / Virus Database: 270.14.103/2558 - Release Date: 12/11/09
10:06:00
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question - not sure if it's possible at all
Hello all, I'm not sure if this can be done in SQL, but if it can, I'd appreciate your help. Let's say I've got this query: SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC) LIMIT ?2 OFFSET ?1 and let's say, Data has an ID field (primary index). Is it possible to find out the *position* in the returned result set of a Data with a given ID? So, let's say the returned result was (just IDs): 1 2 3 4 5 6 9 7 11 14 12 13 10 Can I find out that ID=9 is number 7 (or 6 if zero-based, doesn't matter) using only SQL? Thanks in advance! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question - not sure if it's possible at all
Igor Tandetnik wrote: and let's say, Data has an ID field (primary index). Is it possible to find out the *position* in the returned result set of a Data with a given ID? select count(*) from Data where PlotOnGraph=1 and Date (select Date from Data where ID=?); This works well, until if hits a non-unique date (which happens often in this application). Is there a way to make it work with non-unique dates as well? Thank you! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question - not sure if it's possible at all
Dennis Volodomanov i...@psunrise.com wrote in message news:4a1e80dd.9040...@psunrise.com Igor Tandetnik wrote: and let's say, Data has an ID field (primary index). Is it possible to find out the *position* in the returned result set of a Data with a given ID? select count(*) from Data where PlotOnGraph=1 and Date (select Date from Data where ID=?); This works well, until if hits a non-unique date (which happens often in this application). Is there a way to make it work with non-unique dates as well? Well, there's no way to tell how records with the same Date are going to end up ordered in the original query. If you impose some deterministic order there, e.g. with ORDER BY Date, Id then you can do something like this: select count(*) from Data d, (select Date, ID from Data where ID=?) myRow where PlotOnGraph=1 and d.Date = myRow.Date and (d.Date myRow.Date or d.ID myRow.ID); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question - not sure if it's possible at all
Igor Tandetnik wrote: Well, there's no way to tell how records with the same Date are going to end up ordered in the original query. If you impose some deterministic order there, e.g. with ORDER BY Date, Id then you can do something like this: select count(*) from Data d, (select Date, ID from Data where ID=?) myRow where PlotOnGraph=1 and d.Date = myRow.Date and (d.Date myRow.Date or d.ID myRow.ID); Ok, I'll investigate if I can do anything having that as a base - thank you. Unfortunately the results are sorted by the application (so, the grid control sorts them by date, but it doesn't take the ID in to account, so it's impossible to predict what the order is, as compared to the DB's sorted order). A bit of a mess... Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question - not sure if it's possible at all
Hello all, I'm not sure if this can be done in SQL, but if it can, I'd appreciate your help. Let's say I've got this query: SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC) LIMIT ?2 OFFSET ?1 and let's say, Data has an ID field (primary index). Is it possible to find out the *position* in the returned result set of a Data with a given ID? So, let's say the returned result was (just IDs): 1 2 3 4 5 6 9 7 11 14 12 13 10 Can I find out that ID=9 is number 7 (or 6 if zero-based, doesn't matter) using only SQL? Thanks in advance! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question - not sure if it's possible at all
Dennis Volodomanov i...@psunrise.com wrote: Let's say I've got this query: SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC) LIMIT ?2 OFFSET ?1 Why an extra layer? Why not just SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC LIMIT ?2 OFFSET ?1 and let's say, Data has an ID field (primary index). Is it possible to find out the *position* in the returned result set of a Data with a given ID? select count(*) from Data where PlotOnGraph=1 and Date (select Date from Data where ID=?); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question - not sure if it's possible at all
Igor Tandetnik wrote: Dennis Volodomanov i...@psunrise.com wrote: Let's say I've got this query: SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC) LIMIT ?2 OFFSET ?1 Why an extra layer? Why not just SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC LIMIT ?2 OFFSET ?1 No real reason. I am porting older code and kept it safe (keeping old SQL unchanged for the time being). I'll change that later. and let's say, Data has an ID field (primary index). Is it possible to find out the *position* in the returned result set of a Data with a given ID? select count(*) from Data where PlotOnGraph=1 and Date (select Date from Data where ID=?); Looks interesting - I'll try it later tonight! Thank you for the help. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question, probably stuipid but ...
I can't remember how to escape a ' in a string.
My statement looks something like:
insert . values ('DAY'S AVE' .);
It's been a long day.
Thanks in advance.
J. R.
J. R. Westmoreland
E-mail: j...@jrw.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question, probably stuipid but ...
To escape ' use cosecutive ' , i.e ''
On Wed, 18 Feb 2009 10:32:28 +0530, J. R. Westmoreland j...@jrw.org wrote:
I can't remember how to escape a ' in a string.
My statement looks something like:
insert . values ('DAY'S AVE' .);
It's been a long day.
Thanks in advance.
J. R.
J. R. Westmoreland
E-mail: j...@jrw.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
__
Scanned and protected by Email scanner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question with SQLite
Hello, In a SELECT statement with multiple tables, is it possible to replace WHERE clauses used to avoid cross joins with GROUP BY + HAVING clauses (with the same criteria) for the same purpose (no cross join) ? Are the two methods roughly equivalent in performance ? We ask this because we wonder why using the GROUP BY + HAVING solution with 7 tables instead of WHERE clauses we see big degradation of perforamnce (0,5 seconds against 30 minutes) + break with disk full error message . We checked the statement with GROUP BY + HAVING against the initial WHERE clause and are assured not to miss any criteria present in the WHERE clause. Thanks Jean-Marie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question with SQLite
If you want help with your SQL. You should post it along with your question. There are a lot of really good SQL experts on here that will be able to help you. --- On Tue, 12/9/08, jm cuaz [EMAIL PROTECTED] wrote: From: jm cuaz [EMAIL PROTECTED] Subject: [sqlite] SQL question with SQLite To: sqlite-users@sqlite.org Date: Tuesday, December 9, 2008, 9:58 AM Hello, In a SELECT statement with multiple tables, is it possible to replace WHERE clauses used to avoid cross joins with GROUP BY + HAVING clauses (with the same criteria) for the same purpose (no cross join) ? Are the two methods roughly equivalent in performance ? We ask this because we wonder why using the GROUP BY + HAVING solution with 7 tables instead of WHERE clauses we see big degradation of perforamnce (0,5 seconds against 30 minutes) + break with disk full error message . We checked the statement with GROUP BY + HAVING against the initial WHERE clause and are assured not to miss any criteria present in the WHERE clause. Thanks Jean-Marie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question with SQLite
On Tue, Dec 09, 2008 at 04:58:38PM +0100, jm cuaz scratched on the wall: In a SELECT statement with multiple tables, is it possible to replace WHERE clauses used to avoid cross joins with GROUP BY + HAVING clauses (with the same criteria) for the same purpose (no cross join) ? No. That's what WHERE is for. In fact, if you put all the limits in HAVING, you give the database no choice but to calculate a full cross-join on all the tables. HAVING cannot be used to limit the number of rows in a join. Are the two methods roughly equivalent in performance ? No. For a given query, replacing WHERE with HAVING will usually result in a **significantly** longer run-time, especially if any joins are involved. It will also require a great deal more resources. We ask this because we wonder why using the GROUP BY + HAVING solution with 7 tables instead of WHERE clauses we see big degradation of perforamnce (0,5 seconds against 30 minutes) + break with disk full error message . The FROM and WHERE clauses are tightly bound. The query optimizer understands how to fold WHERE limits into the FROM statement, rejecting many of the rows before they're joined. HAVING is used to post-process the GROUP BY result. If all the limits are in a HAVING clause (and you have no explicit JOIN...ON limits), the system has no choice but to realize a full cross-join of all 7 tables, resulting in an absolutely enormous meta-table. For example, if the row count on your seven tables is 34, 104, 200, 1742, 45, 2, and 15, the database will have to create a temporary table with 1,663,122,240,000 rows (1.66 tera-rows). Cross-joins add up VERY quickly. This is then processed by the GROUP BY (which is going to take a bit of time to chew through all that) and then (and only then) are rows rejected by the HAVING clause. HAVING exists to put limits on the output of GROUP BY. For example, if you group sales by cities and only want to see cities with more than 100 sales, or some such thing. In general, HAVING limits should only be acting on aggregate columns that are a result of the GROUP BY. Any other limits or restrictions should go in the WHERE clause. You should almost never have a HAVING without a GROUP BY. It is completely acceptable (and appropriate) to have a WHERE, GROUP BY, and HAVING all in the same query. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question
Hello, Not specific to sqlite, but a rather generic SQL question... Given a set of ids, what would be the proper way to find the records containing all those ids? Specifically, given a 'document_token' table containing a document_id mapping to multiple token_id, how would one find the document_id which contains all the given token_id? E.g.: create table if not exists document_token ( document_id integer not null, token_idinteger not null ); insert into document_token( 1, 11 ); insert into document_token( 1, 12 ); insert into document_token( 2, 11 ); insert into document_token( 2, 12 ); insert into document_token( 3, 11 ); create temporary table if not exists stage ( id integer not null, ); insert into stage( 11 ); insert into stage( 12 ); Given an input of 11 and 12 as token_id in the 'stage' table, what would be the proper query to retrieve document_id 1 and 2 (but not 3 as it only contains token_id 11)? Simply joining the two tables will result in all the document_id being returned: select distinct document_id fromstage joindocument_token on document_token.token_id = stage.id Thoughts? Thanks in advance. Kind regards, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Petite Abeille [EMAIL PROTECTED] wrote: Given a set of ids, what would be the proper way to find the records containing all those ids? Specifically, given a 'document_token' table containing a document_id mapping to multiple token_id, how would one find the document_id which contains all the given token_id? E.g.: create table if not exists document_token ( document_id integer not null, token_idinteger not null ); create temporary table if not exists stage ( id integer not null, ); insert into stage( 11 ); insert into stage( 12 ); Given an input of 11 and 12 as token_id in the 'stage' table, what would be the proper query to retrieve document_id 1 and 2 (but not 3 as it only contains token_id 11)? select document_id from document_token join stage on (token_id = id) group by document_id having count(*) = (select count(*) from stage); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
I did change it to: UPDATE `table` SET `id` = `id` + 32768 WHERE `id` = x and then I decrement everything over 32768 by 32767 to get it back in line. This was required because a single update on a primary key did return an error about a key conflict when only incrementing by 1! Thanks, Gregor On 16-Jun-08, at 11:53 AM , Darren Duncan wrote: Gregor, why did you do that more complicated version with the subquery and sorting et al? The short version that RBS would have worked a lot better; you just need to say? update binary_report_fmt set column_id = column_id + 1 where column_id 1; ... and then insert a new row with column_id = 2. All that other stuff you did just makes things unnecessarily more complicated, and possibly buggy. On a related matter, UPDATE statements are atomic operations, so the fact that the id is a primary key doesn't matter. Since you're incrementing all the id values simultaneously, there are no duplicate values at any time, so the primary key constraint would remain happy. -- Darren Duncan Gregor Brandt wrote: Hi, thanks this is great. Of course I forgot to mention that the id is a primary key, so I get an error about duplicate primary keys. I tried this: update binary_report_fmt set column_id = column_id + 1 where column_id = (select column_id from binary_report_fmt where column_id = 3 order by column_id desc); but it only updates the last item. I guess I can make it a non- primary key..then it works perfectly. Gregor On 16-Jun-08, at 10:04 AM , [EMAIL PROTECTED] wrote: update table set id = id + 1 WHERE id 1 RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - Gregor Brandt Marionette Software Group Suite 202, 124 - 26 Ave SW Calgary, AB, Canada T2S 3G5 Tel: +1 403 401 4784 EMail: [EMAIL PROTECTED] www.marionette.ca ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question
Sorry about this, but this is a SQL question and not a SQLite specific question. Is there a way to increment a value in a table in-situ. Without reading it, incrementing it, writing it? I need to insert an entry into a table, it has an id, all entries with id's = the id need to be incremented to keep the id's unique ie 1 bob 2 joe 3 irene someone wants to insert 2 sarah, I need the table to look like this 1 bob 2 sarah 3 joe 4 irene Any help would be great. thanks - Gregor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
On 6/16/08, Gregor Brandt [EMAIL PROTECTED] wrote: Sorry about this, but this is a SQL question and not a SQLite specific question. Is there a way to increment a value in a table in-situ. Without reading it, incrementing it, writing it? I need to insert an entry into a table, it has an id, all entries with id's = the id need to be incremented to keep the id's unique ie 1 bob 2 joe 3 irene someone wants to insert 2 sarah, I need the table to look like this -- to keep track of the id that is going to be duplicated UPDATE table SET id = -1 WHERE id = 2 INSERT INTO table VALUES (2, 'sarah'); 1 bob 2 sarah 3 joe 4 irene UPDATE table SET id = id + 1 WHERE id 2 OR id = -1 Any help would be great. thanks - Gregor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
update table set id = id + 1 WHERE id 1 RBS Sorry about this, but this is a SQL question and not a SQLite specific question. Is there a way to increment a value in a table in-situ. Without reading it, incrementing it, writing it? I need to insert an entry into a table, it has an id, all entries with id's = the id need to be incremented to keep the id's unique ie 1 bob 2 joe 3 irene someone wants to insert 2 sarah, I need the table to look like this 1 bob 2 sarah 3 joe 4 irene Any help would be great. thanks - Gregor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Hi, thanks this is great. Of course I forgot to mention that the id is a primary key, so I get an error about duplicate primary keys. I tried this: update binary_report_fmt set column_id = column_id + 1 where column_id = (select column_id from binary_report_fmt where column_id = 3 order by column_id desc); but it only updates the last item. I guess I can make it a non- primary key..then it works perfectly. Gregor On 16-Jun-08, at 10:04 AM , [EMAIL PROTECTED] wrote: update table set id = id + 1 WHERE id 1 RBS Sorry about this, but this is a SQL question and not a SQLite specific question. Is there a way to increment a value in a table in-situ. Without reading it, incrementing it, writing it? I need to insert an entry into a table, it has an id, all entries with id's = the id need to be incremented to keep the id's unique ie 1 bob 2 joe 3 irene someone wants to insert 2 sarah, I need the table to look like this 1 bob 2 sarah 3 joe 4 irene Any help would be great. thanks - Gregor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
On Mon, 16 Jun 2008 10:36:18 -0600, you wrote: Hi, thanks this is great. Of course I forgot to mention that the id is a primary key, so I get an error about duplicate primary keys. I tried this: update binary_report_fmt set column_id = column_id + 1 where column_id = (select column_id from binary_report_fmt where column_id = 3 order by column_id desc); but it only updates the last item. I guess I can make it a non- primary key..then it works perfectly. Gregor Add a large value to all column_id that have to be changed, then subtract that same value minus 1. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Gregor, why did you do that more complicated version with the subquery and sorting et al? The short version that RBS would have worked a lot better; you just need to say? update binary_report_fmt set column_id = column_id + 1 where column_id 1; ... and then insert a new row with column_id = 2. All that other stuff you did just makes things unnecessarily more complicated, and possibly buggy. On a related matter, UPDATE statements are atomic operations, so the fact that the id is a primary key doesn't matter. Since you're incrementing all the id values simultaneously, there are no duplicate values at any time, so the primary key constraint would remain happy. -- Darren Duncan Gregor Brandt wrote: Hi, thanks this is great. Of course I forgot to mention that the id is a primary key, so I get an error about duplicate primary keys. I tried this: update binary_report_fmt set column_id = column_id + 1 where column_id = (select column_id from binary_report_fmt where column_id = 3 order by column_id desc); but it only updates the last item. I guess I can make it a non- primary key..then it works perfectly. Gregor On 16-Jun-08, at 10:04 AM , [EMAIL PROTECTED] wrote: update table set id = id + 1 WHERE id 1 RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Andrea, This appears to do what you want... SQLite version 3.4.2 Enter .help for instructions sqlite sqlite create table tst( name text, score integer, info text ); sqlite insert into tst values( 'A', 289, 'A1' ); sqlite insert into tst values( 'C', 29, 'C1' ); sqlite insert into tst values( 'A', 29, 'A2' ); sqlite insert into tst values( 'C', 129, 'C2' ); sqlite insert into tst values( 'C', 19, 'C3' ); sqlite insert into tst values( 'A', 1129, 'A3' ); sqlite insert into tst values( 'B', 19, 'B1' ); sqlite insert into tst values( 'A', 19, 'A4' ); sqlite insert into tst values( 'B', 9, 'B2' ); sqlite insert into tst values( 'B', 99, 'B3' ); sqlite sqlite select * fro tst; SQL error: near fro: syntax error sqlite select * from tst; A|289|A1 C|29|C1 A|29|A2 C|129|C2 C|19|C3 A|1129|A3 B|19|B1 A|19|A4 B|9|B2 B|99|B3 sqlite select tst.* from tst cross join ... ( select max(score) as maxS, name from tst group by name ) as subQuery ... on tst.name=subQuery.name and tst.score = subQuery.maxS; C|129|C2 A|1129|A3 B|99|B3 sqlite Rgds, Simon 2008/6/6 Andrea Galligani [EMAIL PROTECTED]: Hi to all, I'm a novice in SQL and SQLite so I apologize if this question has an obvious solution I have a table formed in this way. NAME SCORE INFO1 INFO2 etc. Andrew 5 aaa bbb ... Andrew 8 ddd eee ... Paul 4 xxx yyy ... Paul 6 aaa fff ... I need a query to get from any name the row with the max score. So from the above sample, I would like to obtain the following rows. NAME SCORE INFO1 INFO2 etc. Andrew 8 ddd eee ... Paul 6 aaa fff ... Can I get this result using a complex query or should I filter the rows using any different tool from SQL (C/C++)? Thanks in advance Andrea -- -- Andrea Galligani Macs Tech s.r.l. Via San Paolo 11, 56125 Pisa - Italy Phone...: +39 050 40915 e-mail..: [EMAIL PROTECTED] -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
sqlite create table t1(n,c); sqlite insert into t1 values(a,3); sqlite insert into t1 values(a,5); sqlite insert into t1 values(b,7); sqlite insert into t1 values(b,2); sqlite select * from t1; a|3 a|5 b|7 b|2 sqlite select n,max(c) from t1 group by n; a|5 b|7 -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). On Fri, Jun 6, 2008 at 11:03 AM, Andrea Galligani [EMAIL PROTECTED] wrote: Hi to all, I'm a novice in SQL and SQLite so I apologize if this question has an obvious solution I have a table formed in this way. NAME SCORE INFO1 INFO2 etc. Andrew 5 aaa bbb ... Andrew 8 ddd eee ... Paul 4 xxx yyy ... Paul 6 aaa fff ... I need a query to get from any name the row with the max score. So from the above sample, I would like to obtain the following rows. NAME SCORE INFO1 INFO2 etc. Andrew 8 ddd eee ... Paul 6 aaa fff ... Can I get this result using a complex query or should I filter the rows using any different tool from SQL (C/C++)? Thanks in advance Andrea -- -- Andrea Galligani Macs Tech s.r.l. Via San Paolo 11, 56125 Pisa - Italy Phone...: +39 050 40915 e-mail..: [EMAIL PROTECTED] -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Hi Simon, it works very well. Now I study it and I try to understand why it works :-) Thanks a lot Andrea Simon Davies ha scritto: Andrea, This appears to do what you want... SQLite version 3.4.2 Enter .help for instructions sqlite sqlite create table tst( name text, score integer, info text ); sqlite insert into tst values( 'A', 289, 'A1' ); sqlite insert into tst values( 'C', 29, 'C1' ); sqlite insert into tst values( 'A', 29, 'A2' ); sqlite insert into tst values( 'C', 129, 'C2' ); sqlite insert into tst values( 'C', 19, 'C3' ); sqlite insert into tst values( 'A', 1129, 'A3' ); sqlite insert into tst values( 'B', 19, 'B1' ); sqlite insert into tst values( 'A', 19, 'A4' ); sqlite insert into tst values( 'B', 9, 'B2' ); sqlite insert into tst values( 'B', 99, 'B3' ); sqlite sqlite select * fro tst; SQL error: near fro: syntax error sqlite select * from tst; A|289|A1 C|29|C1 A|29|A2 C|129|C2 C|19|C3 A|1129|A3 B|19|B1 A|19|A4 B|9|B2 B|99|B3 sqlite select tst.* from tst cross join ... ( select max(score) as maxS, name from tst group by name ) as subQuery ... on tst.name=subQuery.name and tst.score = subQuery.maxS; C|129|C2 A|1129|A3 B|99|B3 sqlite Rgds, Simon -- -- Andrea Galligani Macs Tech s.r.l. Via San Paolo 11, 56125 Pisa - Italy Phone...: +39 050 40915 e-mail..: [EMAIL PROTECTED] -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL question regarding triggers updating values
Hello all, Let's say I have this schema: CREATE TABLE Table1 (FileID INTEGER NOT NULL, FileOrder INTEGER); And I need to go through it at change FileOrder so that it becomes FileOrder of the next (or previous) FileID and that's FileID FileOrder becomes current (to put it in words - swap them around with either next or previous values), effectively moving them up or down an ordered list. I am thinking about using an UPDATE trigger (so, I update one of FileOrders to become the next one, and the trigger should update the next one to become the current one) - would that be the best solution if I was to try to do it using only SQL? Of course I can do it in the program, but I think that doing it inside SQLite would be a bit faster? Is there any other way to do this using SQL? Thanks in advance for your ideas/suggestions :) Regards, Dennis - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sql question
I want to recycle the table for which I need to be able to delete the first 100 records from a table and add 100 new records. Thanks
Re: [sqlite] sql question
Uma Venkataraman wrote: I want to recycle the table for which I need to be able to delete the first 100 records from a table and add 100 new records. Thanks Uma, See my reply to a similar question about FIFO tables in the archives at http://article.gmane.org/gmane.comp.db.sqlite.general/16175/match=fifo HTH Dennis Cote
[sqlite] SQL question - grouping records and more
Hi, I have table with the fields id, name and number. In the table there are several records with the same id and name but different numbers. For example: First record - id: 1, name: John, number: 5 Second record - id: 1, name: Joe, number: 4 Third record - id: 2, name: Richard, number: 1 I want to select records from the table by grouping with id field. I use groupby for this. But i also want to see all the number fields' datas in a group side by side. I have written down the list which i want to see after the query. First record - id: 1, name: John, number: 54 Second record - id: 2, name Richard, number: 1 Is there any idea about how to write a query to get this result? Thanks in advace. -- Veysel Harun Sahin
[sqlite] SQL question
I have 2 related table: CREATE TABLE Ingredients( ID INTEGER PRIMARY KEY, name TEXT, description BLOB, property_ID INTEGER ); CREATE TABLE Properties( ID INTEGER PRIMARY KEY, price double ); When I create a new Ingredient I would like to create a new property for this ingredient and setup its property_ID. because there is no stored procedure I was thinking to use a trigger like this one: CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients FOR EACH ROW BEGIN INSERT INTO Properties () VALUES (); UPDATE Ingredients SET property_ID=ROWID WHERE ID=OLD.ID; END; but it doesn't work I could do with some help for the SQL syntax :D
Re: [sqlite] SQL question
BTW, one more question / precision. in INSERT INTO Properties() I didn't pass the value for ID. because basically I want an auto-incremented value which I don't have to worry about. maybe that's not the way to use such value ?! - Original Message - From: Lloyd Dupont [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, June 08, 2005 10:37 PM Subject: [sqlite] SQL question I have 2 related table: CREATE TABLE Ingredients( ID INTEGER PRIMARY KEY, name TEXT, description BLOB, property_ID INTEGER ); CREATE TABLE Properties( ID INTEGER PRIMARY KEY, price double ); When I create a new Ingredient I would like to create a new property for this ingredient and setup its property_ID. because there is no stored procedure I was thinking to use a trigger like this one: CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients FOR EACH ROW BEGIN INSERT INTO Properties () VALUES (); UPDATE Ingredients SET property_ID=ROWID WHERE ID=OLD.ID; END; but it doesn't work I could do with some help for the SQL syntax :D
Re: [sqlite] SQL question
Hi, i am not sure if i understand your problem correctly. Perhaps the following does it: CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients FOR EACH ROW BEGIN INSERT INTO Properties (price) VALUES (some price); UPDATE Ingredients SET property_ID (select max(id) from properties) WHERE ID=OLD.ID; END; Because the id is omitted in the Insert - Statement to 'properties', sqlite supplies one, which you can then update into ingredients. However, I'm not clear where the price is supposed to come from. Martin Lloyd Dupont schrieb: BTW, one more question / precision. in INSERT INTO Properties() I didn't pass the value for ID. because basically I want an auto-incremented value which I don't have to worry about. maybe that's not the way to use such value ?! - Original Message - From: Lloyd Dupont [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, June 08, 2005 10:37 PM Subject: [sqlite] SQL question I have 2 related table: CREATE TABLE Ingredients( ID INTEGER PRIMARY KEY, name TEXT, description BLOB, property_ID INTEGER ); CREATE TABLE Properties( ID INTEGER PRIMARY KEY, price double ); When I create a new Ingredient I would like to create a new property for this ingredient and setup its property_ID. because there is no stored procedure I was thinking to use a trigger like this one: CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients FOR EACH ROW BEGIN INSERT INTO Properties () VALUES (); UPDATE Ingredients SET property_ID=ROWID WHERE ID=OLD.ID; END; but it doesn't work I could do with some help for the SQL syntax :D
Re: [sqlite] SQL question
thanks Martin it worked! although I replaced your (SELECT MAX(ID) FROM Properties) by ROWID. is it sound? like that: CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients BEGIN INSERT INTO Properties (price) VALUES (NULL); UPDATE Ingredients SET property_ID = ROWID WHERE ID=OLD.ID; END; -- price doesn't matter. I set it up much later.. (whenever the user want, in fact), but I want my property line created and correctly linked!
Re: [sqlite] SQL question
Hi Lloyd, i am not sure, but i checked the documentation and i don't think it ist sound. In your Update, the ROWID semms to refer to ingredients.rowid. However, you want to set properties.rowid. Can it be that it works, because Properties and Ingredients happen to have the same number of rows? Martin Lloyd Dupont schrieb: thanks Martin it worked! although I replaced your (SELECT MAX(ID) FROM Properties) by ROWID. is it sound? like that: CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients BEGIN INSERT INTO Properties (price) VALUES (NULL); UPDATE Ingredients SET property_ID = ROWID WHERE ID=OLD.ID; END; -- price doesn't matter. I set it up much later.. (whenever the user want, in fact), but I want my property line created and correctly linked!
Re: [sqlite] SQL question
Martin Engelschalk [EMAIL PROTECTED] writes: Hi Lloyd, i am not sure, but i checked the documentation and i don't think it ist sound. In your Update, the ROWID semms to refer to ingredients.rowid. However, you want to set properties.rowid. Can it be that it works, because Properties and Ingredients happen to have the same number of rows? Martin Lloyd Dupont schrieb: thanks Martin it worked! although I replaced your (SELECT MAX(ID) FROM Properties) by ROWID. is it sound? like that: CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients BEGIN INSERT INTO Properties (price) VALUES (NULL); UPDATE Ingredients SET property_ID = ROWID WHERE ID=OLD.ID; END; According to the documentation, you should be able to use the last_insert_rowid() function: CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients BEGIN INSERT INTO Properties (price) VALUES (NULL); UPDATE Ingredients SET property_ID = last_insert_rowid() WHERE ID=OLD.ID; END; Derrell
Re: [sqlite] SQL question
Thanks for that! last_insert_rowid() function: anyway, I hadn't tested the code. I mean the CREATE TRIGGER succeed. But I didn't check if the trigger itself works well. Now I did and have a problem... It don't work! I get: SQLite Error 1 - no such column: OLD.ID this is my setting: CREATE TABLE Ingredients( ID INTEGER PRIMARY KEY, name TEXT, description BLOB, property_ID INTEGER ); CREATE TABLE Properties( ID INTEGER PRIMARY KEY, price double, calories double ); CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients BEGIN INSERT INTO Properties (price) VALUES (NULL); UPDATE Ingredients SET property_ID = last_insert_rowid() WHERE ID=OLD.ID; END; -- it looks like OLD is now the newly inserted line in Properties and not the on in Ingredients ?!?! is it a bug or a feature? how to work around it?
Re: [sqlite] SQL question
Sorry, stupit mistak, I have to use NEW and not OLD in case of an INSERT trigger! Thanks all it works like a breeze! - Original Message - From: Lloyd Dupont [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Thursday, June 09, 2005 12:26 AM Subject: Re: [sqlite] SQL question Thanks for that! last_insert_rowid() function: anyway, I hadn't tested the code. I mean the CREATE TRIGGER succeed. But I didn't check if the trigger itself works well. Now I did and have a problem... It don't work! I get: SQLite Error 1 - no such column: OLD.ID this is my setting: CREATE TABLE Ingredients( ID INTEGER PRIMARY KEY, name TEXT, description BLOB, property_ID INTEGER ); CREATE TABLE Properties( ID INTEGER PRIMARY KEY, price double, calories double ); CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients BEGIN INSERT INTO Properties (price) VALUES (NULL); UPDATE Ingredients SET property_ID = last_insert_rowid() WHERE ID=OLD.ID; END; -- it looks like OLD is now the newly inserted line in Properties and not the on in Ingredients ?!?! is it a bug or a feature? how to work around it?
[sqlite] SQL Question
Hello, I know this is not directly related to SQLite, but hopefully someone has the time to help me with a (probably) basic SQL question: Consider the following tables: CREATE TABLE teams (id,name); CREATE TABLE games (id, date, team1_id, team2_id, result); team1_id and team2_id refer to the id in the teams table. 1. What query would be best suited to get an output so that the output would contain the teams names (not only the id) and the dates and results? 2. What query would be best if I want to search for games that two specific teams had played. For example, I want all the games that the teams with the names 'bla' and 'blub' played against each other. Is there any way to do that? I tried this with joins, with groupings, but wasn't able to get the lists i wanted. I can get the name of one team, but not the name of the other team. Any help would be appreciated Gilbert
Re: [sqlite] SQL Question
CREATE TABLE teams (id,name);
CREATE TABLE games (id, date, team1_id, team2_id, result);
team1_id and team2_id refer to the id in the teams table.
1. What query would be best suited to get an output so that the
output would contain the teams names (not only the id) and the
dates and results?
select team1.name, team2.name, games.date, games.result
fromgames
inner join teams team1 on games.team1_id = team1.id
inner join teams team2 on games.team2_id = team2.id;
2. What query would be best if I want to search for games that
two specific teams had played. For example, I want all the games
that the teams with the names 'bla' and 'blub' played against
each other. Is there any way to do that?
select team1.name, team2.name, games.date, games.result
fromgames
inner join teams team1 on games.team1_id = team1.id
and team1.name in ('bla', 'blub')
inner join teams team2 on games.team2_id = team2.id
and team2.name in ('bla', 'blub');
I didn't check spelling or stuff, but the syntax should be mostly correct.
