Re: [sqlite] is this possible
On 29 Mar 2019, at 1:55pm, Dan Kennedy wrote: > The error doesn't occur unless you actually query the view though, correct? My theory on SQLite was that the VIEW definition was only syntax-checked and not compiled in any way. So you could do CREATE VIEW v as select * from t ORDER BY a; CREATE TABLE t (a INTEGER UNIQUE); ... use view v a few times ... DROP TABLE t; CREATE TABLE t (a TEXT, b REAL); ... use view v a few more times ... changing what the view meant, whether a temporary index was needed, and how many columns would be returned, without having to recreate the view. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
On 3/29/2019 9:55 AM, Dan Kennedy wrote: On 29/3/62 03:00, Igor Tandetnik wrote: On 3/28/2019 3:21 PM, Mark Wagner wrote: Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema. The error doesn't occur unless you actually query the view though, correct? Yes, I think the tool I was using tries to use the view right after opening the database, probably to figure out what columns it offers. This led me to erroneously conclude that merely opening the database triggers the error. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
On 29/3/62 03:00, Igor Tandetnik wrote: On 3/28/2019 3:21 PM, Mark Wagner wrote: Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema. The error doesn't occur unless you actually query the view though, correct? Here, I get: $ ./sqlite3 x.db SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> CREATE TABLE t (foo); sqlite> CREATE TABLE s (bar); sqlite> CREATE VIEW v as select * from t join s on (foo = q); sqlite> $ ./sqlite3 x.db SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> SELECT * FROM t; sqlite> SELECT * FROM s; sqlite> SELECT * FROM v; Error: no such column: q Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
On Thursday, 28 March, 2019 13:21, Mark Wagner wrote: >Imagine I have these two tables and one view defining a join. >CREATE TABLE t (foo); >CREATE TABLE s (bar); >CREATE VIEW v as select * from t join s on (foo = q); >I appear to be able to do this query: >select 20 as q, * from t join s on (foo=q); Really, this is: select 20 as q, * from t, s where foo == q; q is an alias for the constant 20. So what you are really saying is this: select 20 as q, * from t, s where foo == 20; which is valid. >But apparently I cannot do this: >sqlite> select 20 as q, * from v; >Error: no such column: q No, because q is not a column in either t or s. >It's interesting because it allows me to define the view and at that >point it knows nothing about q so I would have assumed it could be >"supplied" later. Defining a View is nothing more than storing the definition of the view in a table. It is not parsed until you use it (though it is syntax checked so if you make a syntax error you will be told about then when you attempt to create the view). You can define the view before defining the tables s and t, or after, or betwixt defining them. You can even drop the tables (either or both) and recreate them (or not). However, at the time you want to EXECUTE (use) the view v the tables s and t must exist (or you will get a no such table error), and the columns foo and q must be defined in one of those tables (or you get a no such column error). >Is this just how it is or perhaps my syntax is off? Or maybe I'm >just confused. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
On 3/28/2019 3:21 PM, Mark Wagner wrote: Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema. I don't believe you can create a parameterized view. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
From the page https://sqlite.org/lang_createview.html : "The CREATE VIEW command assigns a name to a pre-packaged SELECT statement ..." So, it seems that the command "CREATE VIEW" just creates a name for a SELECT statement, and checks nothing more than syntax. On 28.3.2019 21:21, Mark Wagner wrote: CREATE VIEW v as select * from t join s on (foo = q); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] is this possible
Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); I appear to be able to do this query: select 20 as q, * from t join s on (foo=q); But apparently I cannot do this: sqlite> select 20 as q, * from v; Error: no such column: q It's interesting because it allows me to define the view and at that point it knows nothing about q so I would have assumed it could be "supplied" later. Is this just how it is or perhaps my syntax is off? Or maybe I'm just confused. Just curious. Thanks! -- Mark ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible?
On Sun, Mar 1, 2009 at 4:43 PM, Mike Yenco wrote: > > On Mar 1, 2009, at 5:18 PM, P Kishor wrote: > >> It is going to get even more complicated and soon approach Igor-level >> of complexity. So, for sanity's sake, it would probably be done better >> in your application. > > Thanks for the explanation. > > Yeah, as I said in one of my previous emails, my first inclination was to > set this up in a repeat loop in my project with individual calls to SQLite > for each "container". Mike, You don't have to make repeat calls to SQLite. Depending on the size of your dataset, you can grab entire chunks for your use, and then iterate over them in your application. Of course, this won't work if you are on some single-chip, EPROM type hardware with only assembly and 5K of RAM available to you. I am talking about a normal PC type situation here. Also, balance the complexity and repetition of SQLite calls with your own sanity. Trust me -- SQLite will not complain if you send inefficient or repetitive queries to it. The CPU might warm up a little, but for the most part, premature optimization is a serious waste of time. There are frequently questions on this list that ask what all should be considered to make a very efficient application with 5000 rows in the dataset, or something pointless like that. In most of those situations, going ahead an building the app would probably yield better insight than asking the list without trying something first. SQLite is a terrific little datastore, quickly and faithfully returning what you put into it. Most of the manipulation of the data, esp. for display purposes, is best done in the application. Good luck. > > I just wanted to make sure there wasn't some more efficient way to do it > with a single SQLite call. Turns out there is based on the result Martin > came up with, but the complexity... and added complexity you suggest I would > run into going forward if I want to do more definitely seems to work > against any benefit it might have had. So I think, for sanity's sake (er... > might already be too late for that :-) I'll take a break, take a couple of > aspirin and then I'll go with my first impulse and just chalk this up as a > nice diverting (slightly headache inducing) learning experience. > > -Mike > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible?
On Mar 1, 2009, at 5:18 PM, P Kishor wrote: > It is going to get even more complicated and soon approach Igor-level > of complexity. So, for sanity's sake, it would probably be done better > in your application. Thanks for the explanation. Yeah, as I said in one of my previous emails, my first inclination was to set this up in a repeat loop in my project with individual calls to SQLite for each "container". I just wanted to make sure there wasn't some more efficient way to do it with a single SQLite call. Turns out there is based on the result Martin came up with, but the complexity... and added complexity you suggest I would run into going forward if I want to do more definitely seems to work against any benefit it might have had. So I think, for sanity's sake (er... might already be too late for that :-) I'll take a break, take a couple of aspirin and then I'll go with my first impulse and just chalk this up as a nice diverting (slightly headache inducing) learning experience. -Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible?
On Sun, Mar 1, 2009 at 4:04 PM, Mike Yenco wrote: > > On Mar 1, 2009, at 2:15 PM, Martin Engelschalk wrote: > >> Hi Mike, >> >> Yes, this is possible, however, it is complicated. > > Hi Martin, > > Thanks for the reply. It usually is complicated :-) > > >> select Name, case OrderValue when 0 then NULL else ContainerId end as >> ContainerId >> from >> ( >> select distinct ContainerName as Name, Container.ContainerId as >> ContainerId, 0 as OrderValue >> from Contents join Container on Container.ContainerId = >> Contents.ContainerId >> where ContentName like '%app%' >> union >> select ContentName as Name, Contents.ContainerId as ContainerId, 1 as >> OrderValue >> from Contents join Container on Container.ContainerId = >> Contents.ContainerId >> where ContentName like '%app%' >> order by 2,3 >> ) > > I had simplified my example a bit, but figured out enough of what you > did here to replace the example data with some of the data in my > project to see that it works (fantastic). The only thing now is, I > was hoping by going with a simpler example I would be able to > understand what was suggested. But as you say... it is complicated. > > Is there any chance you can break down what is taking place here into > english so I can maybe get a better sense of what this is doing? > > For example, I'm trying to figure this out so I could maybe adjust it > in some ways such as: > > - returning more columns from Contents > - sorting the results so the Containers are in alphabetical order > followed by the Contents of each in alphabetical order. > - maybe getting a total count of the number of Contents returned > - expanding the search to include multiple columns and criteria. > > I know how to do the above things in rather simple SQLite syntax, but > given the complexity of this I'm not sure where to add such syntax or > if it is even possible in such a structure to add such syntax. Any > pointers that might get me going in the right direction? work from inside out. Martin's solution has three queries (three SELECT statements). Run them individually from inside out. So -- query1a: SELECT DISTINCT ContainerName AS Name, Container.ContainerId AS ContainerId, 0 AS OrderValue FROM Contents JOIN Container ON Container.ContainerId = Contents.ContainerId WHERE ContentName like '%app%' query1b: SELECT ContentName AS Name, Contents.ContainerId AS ContainerId, 1 AS OrderValue FROM Contents JOIN Container ON Container.ContainerId = Contents.ContainerId WHERE ContentName like '%app%' The two queries return a set of rows each. You UNION the two together to give you a larger set, that is, a set with both sets of rows, ORDERed by 2nd and 3rd columns in the set. Then you query that set with query2: SELECT Name, CASE OrderValue WHEN 0 THEN NULL ELSE ContainerId END AS ContainerId FROM (the set from UNION above) It is going to get even more complicated and soon approach Igor-level of complexity. So, for sanity's sake, it would probably be done better in your application. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible?
On Mar 1, 2009, at 2:15 PM, Martin Engelschalk wrote: > Hi Mike, > > Yes, this is possible, however, it is complicated. Hi Martin, Thanks for the reply. It usually is complicated :-) > select Name, case OrderValue when 0 then NULL else ContainerId end as > ContainerId > from > ( > select distinct ContainerName as Name, Container.ContainerId as > ContainerId, 0 as OrderValue > from Contents join Container on Container.ContainerId = > Contents.ContainerId > where ContentName like '%app%' > union > select ContentName as Name, Contents.ContainerId as ContainerId, 1 as > OrderValue > from Contents join Container on Container.ContainerId = > Contents.ContainerId > where ContentName like '%app%' > order by 2,3 > ) I had simplified my example a bit, but figured out enough of what you did here to replace the example data with some of the data in my project to see that it works (fantastic). The only thing now is, I was hoping by going with a simpler example I would be able to understand what was suggested. But as you say... it is complicated. Is there any chance you can break down what is taking place here into english so I can maybe get a better sense of what this is doing? For example, I'm trying to figure this out so I could maybe adjust it in some ways such as: - returning more columns from Contents - sorting the results so the Containers are in alphabetical order followed by the Contents of each in alphabetical order. - maybe getting a total count of the number of Contents returned - expanding the search to include multiple columns and criteria. I know how to do the above things in rather simple SQLite syntax, but given the complexity of this I'm not sure where to add such syntax or if it is even possible in such a structure to add such syntax. Any pointers that might get me going in the right direction? -Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible?
Hi Mike,
Yes, this is possible, however, it is complicated.
Lets first create the tables an populate them:
create table Container (ContainerName text, ContainerId text primary key)
create table Contents (ContentName text, ContainerId text)
insert into Container values ('Fruit', 'ABC-0001');
insert into Container values ('Vegetable', 'ABC-0002');
insert into Container values ('Computer', 'ABC-0003');
insert into Contents values ('Orange (Navel)', 'ABC-0001');
insert into Contents values ('Apple (Baldwin)', 'ABC-0001');
insert into Contents values ('Apple (Granny Smith)', 'ABC-0001');
insert into Contents values ('Broccoli', 'ABC-0002');
insert into Contents values ('Carrot', 'ABC-0002');
insert into Contents values ('Picture 1.png', 'ABC-0003');
insert into Contents values ('Safari.app', 'ABC-0003');
insert into Contents values ('Portfolio.psd', 'ABC-0003');
insert into Contents values ('iTunes.app', 'ABC-0003');
The following select gets you the desired result. Note that your
condition on the ContentName ( where ContentName like '%app%' ) appeares
twice. I could not find a way to avoid this.
select Name, case OrderValue when 0 then NULL else ContainerId end as
ContainerId
from
(
select distinct ContainerName as Name, Container.ContainerId as
ContainerId, 0 as OrderValue
from Contents join Container on Container.ContainerId = Contents.ContainerId
where ContentName like '%app%'
union
select ContentName as Name, Contents.ContainerId as ContainerId, 1 as
OrderValue
from Contents join Container on Container.ContainerId =
Contents.ContainerId
where ContentName like '%app%'
order by 2,3
)
Martin
Mike Yenco wrote:
> OK, let's see if I can clarify this.
>
> I'm using "blank", "Group", and "Code" as placeholders here. Blank is
> a column that contains nothing (An empty placeholder I need for
> display purposes). Group could be any text. Code is some ID text.
>
> Table B is being written to by some third-party code which I can't
> modify. Ideally I would put the "blank" I want into Table B, but
> unfortunately the third-party code can't cope with that.
>
> If it helps, let me fill in some example data:
>
> Table A ... let's call it "Container"
> "" | "Fruit" | "ABC-0001"
> "" | "Vegetable" | "ABC-0002"
> "" | "Computer" | "ABC-0003"
>
> Table B ... let's call it "Contents"
> "Orange (Navel)" | "ABC-0001"
> "Apple (Baldwin)" | "ABC-0001"
> "Apple (Granny Smith)" | "ABC-0001"
> "Broccoli" | "ABC-0002"
> "Carrot" | "ABC-0002"
> "Picture 1.png" | "ABC-0003"
> "Safari.app" | "ABC-0003"
> "Portfolio.psd" | "ABC-0003"
> "iTunes.app" | "ABC-0004"
>
> Now let's say I'm looking for as my search anything that contains
> "app" in the first column of table B. I'm looking for results that
> group the matching items.
>
> "Fruit"
> "" | "Apple (Baldwin)" | "ABC-0001"
> "" | "Apple (Granny Smith)" | "ABC-0001"
> "Computer"
> "" | "Safari.app" | "ABC-0003"
> "" | "iTunes.app" | "ABC-003"
>
> The rest do not contain "app". There is no need to display the
> "Vegetable" container as there were no contents found for that one.
>
> My first inclination was to go with a repeat loop... take the number
> of lines in Table A and loop through each one doing a search of
> contents for my search term... and if anything is returned, displaying
> the line from Table A followed by any matches. But ideally it seems
> there should be some way to do this with a single call to SQLite.
>
> If grabbing a blank from table A to put before each line of table B is
> too difficult then... I can probably work around that. But I would
> still need a way to get the following at a minimum:
>
> "Fruit"
> "Apple (Baldwin)" | "ABC-0001"
> "Apple (Granny Smith)" | "ABC-0001"
> "Computer"
> "Safari.app" | "ABC-0003"
> "iTunes.app" | "ABC-0003"
>
> If the blank has to be duplicated to make an equal number of items
> returned that would also be fine:
>
> "Fruit" | ""
> "Apple (Baldwin)" | "ABC-0001"
> "Apple (Granny Smith)" | "ABC-0001"
> "Computer" | ""
> "Safari.app" | "ABC-0003"
> "iTunes.app" | "ABC-0003"
>
>
> Possible?
>
> -Mike
>
>
> On Mar 1, 2009, at 12:29 PM, Martin Engelschalk wrote:
>
>
>> Hi Mike,
>>
>> sorry, i don't fully get it. However, a single SQL statement can not
>> return data rows of different structure as you indicated in your
>> example.
>> Especially, I am confused about "blank". It seems to come from Table
>> A,
>> same as "Group *". Do you want to put "Group *" as a sort of headline,
>> but not "blank" ?
>>
>> Martin
>>
>
> ___
> 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] Is this possible?
OK, let's see if I can clarify this. I'm using "blank", "Group", and "Code" as placeholders here. Blank is a column that contains nothing (An empty placeholder I need for display purposes). Group could be any text. Code is some ID text. Table B is being written to by some third-party code which I can't modify. Ideally I would put the "blank" I want into Table B, but unfortunately the third-party code can't cope with that. If it helps, let me fill in some example data: Table A ... let's call it "Container" "" | "Fruit" | "ABC-0001" "" | "Vegetable" | "ABC-0002" "" | "Computer" | "ABC-0003" Table B ... let's call it "Contents" "Orange (Navel)" | "ABC-0001" "Apple (Baldwin)" | "ABC-0001" "Apple (Granny Smith)" | "ABC-0001" "Broccoli" | "ABC-0002" "Carrot" | "ABC-0002" "Picture 1.png" | "ABC-0003" "Safari.app" | "ABC-0003" "Portfolio.psd" | "ABC-0003" "iTunes.app" | "ABC-0004" Now let's say I'm looking for as my search anything that contains "app" in the first column of table B. I'm looking for results that group the matching items. "Fruit" "" | "Apple (Baldwin)" | "ABC-0001" "" | "Apple (Granny Smith)" | "ABC-0001" "Computer" "" | "Safari.app" | "ABC-0003" "" | "iTunes.app" | "ABC-003" The rest do not contain "app". There is no need to display the "Vegetable" container as there were no contents found for that one. My first inclination was to go with a repeat loop... take the number of lines in Table A and loop through each one doing a search of contents for my search term... and if anything is returned, displaying the line from Table A followed by any matches. But ideally it seems there should be some way to do this with a single call to SQLite. If grabbing a blank from table A to put before each line of table B is too difficult then... I can probably work around that. But I would still need a way to get the following at a minimum: "Fruit" "Apple (Baldwin)" | "ABC-0001" "Apple (Granny Smith)" | "ABC-0001" "Computer" "Safari.app" | "ABC-0003" "iTunes.app" | "ABC-0003" If the blank has to be duplicated to make an equal number of items returned that would also be fine: "Fruit" | "" "Apple (Baldwin)" | "ABC-0001" "Apple (Granny Smith)" | "ABC-0001" "Computer" | "" "Safari.app" | "ABC-0003" "iTunes.app" | "ABC-0003" Possible? -Mike On Mar 1, 2009, at 12:29 PM, Martin Engelschalk wrote: > Hi Mike, > > sorry, i don't fully get it. However, a single SQL statement can not > return data rows of different structure as you indicated in your > example. > Especially, I am confused about "blank". It seems to come from Table > A, > same as "Group *". Do you want to put "Group *" as a sort of headline, > but not "blank" ? > > Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible?
On Sun, Mar 1, 2009 at 11:14 AM, Mike Yenco wrote: > Ok, I guess this list doesn't support image attachments. Let's see if > I can sort of illustrate what I'm looking to do in text without > requiring 1000 words. > > Table A > blank | Group 1 | Code 1 > blank | Group 2 | Code 2 > blank | Group 3 | Code 3 > > Table B > Item 1 | Code 1 > Item 2 | Code 1 > Item 3 | Code 1 > Item 4 | Code 2 > Item 5 | Code 2 > Item 6 | Code 3 > Item 7 | Code 3 > Item 8 | Code 3 > Item 9 | Code 3 > > > Looking for a way to get a result something like below where a search > of Table B for items containing some particular text will return the > data in a format like this: > > Group 1 > blank | Item 2 | Code 1 > blank | Item 3 | Code 1 > Group 3 > blank | Item 7 | Code 3 > blank | Item 9 | Code 3 > First, you have to understand that a SQL query returns a "rectangular" set, that is, rows by columns. The above is "presentational," that is, for display purposes, and you have to do that in your application. The best you could hope for is something like Group 1|blank|blank blank| Item 2 | Code 1 blank| Item 3 | Code 1 Group 3|blank|blank blank| Item 7 | Code 3 blank| Item 9 | Code 3 All that said, the above still doesn't make sense. What happened to blank |Item 1 | Code 1 or blank |Item 8 | Code 3 or the entire Group 2? > > -Mike > > > On Mar 1, 2009, at 11:57 AM, Mike Yenco wrote: > >> Is there a way that SQLite can return all matching items to a search >> string in Table B, but return a group name from Table A before each >> set of matching items within the result? (see attached image). >> >> >> ___ >> 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 > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible?
Hi Mike, sorry, i don't fully get it. However, a single SQL statement can not return data rows of different structure as you indicated in your example. Especially, I am confused about "blank". It seems to come from Table A, same as "Group *". Do you want to put "Group *" as a sort of headline, but not "blank" ? Martin Mike Yenco wrote: > Ok, I guess this list doesn't support image attachments. Let's see if > I can sort of illustrate what I'm looking to do in text without > requiring 1000 words. > > Table A > blank | Group 1 | Code 1 > blank | Group 2 | Code 2 > blank | Group 3 | Code 3 > > Table B > Item 1 | Code 1 > Item 2 | Code 1 > Item 3 | Code 1 > Item 4 | Code 2 > Item 5 | Code 2 > Item 6 | Code 3 > Item 7 | Code 3 > Item 8 | Code 3 > Item 9 | Code 3 > > > Looking for a way to get a result something like below where a search > of Table B for items containing some particular text will return the > data in a format like this: > > Group 1 > blank | Item 2 | Code 1 > blank | Item 3 | Code 1 > Group 3 > blank | Item 7 | Code 3 > blank | Item 9 | Code 3 > > > -Mike > > > On Mar 1, 2009, at 11:57 AM, Mike Yenco wrote: > > >> Is there a way that SQLite can return all matching items to a search >> string in Table B, but return a group name from Table A before each >> set of matching items within the result? (see attached image). >> >> >> ___ >> 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] Is this possible?
Ok, I guess this list doesn't support image attachments. Let's see if I can sort of illustrate what I'm looking to do in text without requiring 1000 words. Table A blank | Group 1 | Code 1 blank | Group 2 | Code 2 blank | Group 3 | Code 3 Table B Item 1 | Code 1 Item 2 | Code 1 Item 3 | Code 1 Item 4 | Code 2 Item 5 | Code 2 Item 6 | Code 3 Item 7 | Code 3 Item 8 | Code 3 Item 9 | Code 3 Looking for a way to get a result something like below where a search of Table B for items containing some particular text will return the data in a format like this: Group 1 blank | Item 2 | Code 1 blank | Item 3 | Code 1 Group 3 blank | Item 7 | Code 3 blank | Item 9 | Code 3 -Mike On Mar 1, 2009, at 11:57 AM, Mike Yenco wrote: > Is there a way that SQLite can return all matching items to a search > string in Table B, but return a group name from Table A before each > set of matching items within the result? (see attached image). > > > ___ > 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] Is this possible?
Hi Mike, you can't use attachments in this mailing list. Best post your data model, some data and the desired result. Martin Mike Yenco wrote: > Is there a way that SQLite can return all matching items to a search > string in Table B, but return a group name from Table A before each > set of matching items within the result? (see attached image). > > > > > ___ > 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] Is this possible?
Is there a way that SQLite can return all matching items to a search string in Table B, but return a group name from Table A before each set of matching items within the result? (see attached image). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this possible in SQLite?
John O'Neill wrote: Hi Dennis, Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table. So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value. John, No you can't combine SELECT and UPDATE directly. However, the insert statements I gave do change the id from its value of 1 in table a or b to a new value of 2 in table acopy or bcopy. INSERT INTO acopy SELECT 2, data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 2, data FROM b WHERE id = 1; Rather than selecting the entire row that matches the old id, I select the new id and the data field from the row in the old table, and then insert them into the new table. This works for your simple example, but it may not be general enough depending upon how the values of the new id are generated. Dennis Cote
RE: [sqlite] Is this possible in SQLite?
Unfortunately, as the syntax on the website points out, this is not supported. As you pointed out previously, it is doable with a few extra commands. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: John O'Neill [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 3:48 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Is this possible in SQLite? Hi Dennis, Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table. So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value. I guess this question is can I combine an UPDATE...SET with an INSERT...SELECT command? Thanks, John -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 4:32 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this possible in SQLite? John O'Neill wrote: >Hello all, > >I have a fairly simple DB with two tables. I'm trying to combine a >SELECT and UPDATE command, if it is possible: > >CREATE TABLE a (id PRIMARY KEY, data INT); >CREATE TABLE b (id INT, data INT); > >INSERT INTO a VALUES( 1, 100 ); >INSERT INTO b VALUES( 1, 101 ); >INSERT INTO b VALUES( 1, 102 ); >INSERT INTO b VALUES( 1, 103 ); >... > >And at some point in the future, two new tables (possibly in a >different database) are created: > >CREATE TABLE acopy (id PRIMARY KEY, data INT); >CREATE TABLE bcopy (id INT, data INT ); > >Is there a way to do the following: > >INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X >); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); > >Instead of doing the following 4 commands or SELECTing a and b into >TEMP tables: > >UPDATE a SET id = X WHERE id = 1; >UPDATE b SET id = X WHERE id = 1; >INSERT INTO acopy SELECT * FROM a; >INSERT INTO bcopy SELECT * FROM b; > >Thanks, >John > > > > > John, The following will do what you have asked (I substituted the letter 'X' for your new value X for clarity), but I'm not sure if this is general enough for your real needs. CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote
RE: [sqlite] Is this possible in SQLite?
Hi Dennis, Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table. So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value. I guess this question is can I combine an UPDATE...SET with an INSERT...SELECT command? Thanks, John -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 4:32 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this possible in SQLite? John O'Neill wrote: >Hello all, > >I have a fairly simple DB with two tables. I'm trying to combine a >SELECT and UPDATE command, if it is possible: > >CREATE TABLE a (id PRIMARY KEY, data INT); >CREATE TABLE b (id INT, data INT); > >INSERT INTO a VALUES( 1, 100 ); >INSERT INTO b VALUES( 1, 101 ); >INSERT INTO b VALUES( 1, 102 ); >INSERT INTO b VALUES( 1, 103 ); >... > >And at some point in the future, two new tables (possibly in a >different database) are created: > >CREATE TABLE acopy (id PRIMARY KEY, data INT); >CREATE TABLE bcopy (id INT, data INT ); > >Is there a way to do the following: > >INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X >); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); > >Instead of doing the following 4 commands or SELECTing a and b into >TEMP tables: > >UPDATE a SET id = X WHERE id = 1; >UPDATE b SET id = X WHERE id = 1; >INSERT INTO acopy SELECT * FROM a; >INSERT INTO bcopy SELECT * FROM b; > >Thanks, >John > > > > > John, The following will do what you have asked (I substituted the letter 'X' for your new value X for clarity), but I'm not sure if this is general enough for your real needs. CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote
Re: [sqlite] Is this possible in SQLite?
John O'Neill wrote: Hello all, I have a fairly simple DB with two tables. I'm trying to combine a SELECT and UPDATE command, if it is possible: CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); ... And at some point in the future, two new tables (possibly in a different database) are created: CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); Is there a way to do the following: INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X ); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); Instead of doing the following 4 commands or SELECTing a and b into TEMP tables: UPDATE a SET id = X WHERE id = 1; UPDATE b SET id = X WHERE id = 1; INSERT INTO acopy SELECT * FROM a; INSERT INTO bcopy SELECT * FROM b; Thanks, John John, The following will do what you have asked (I substituted the letter 'X' for your new value X for clarity), but I'm not sure if this is general enough for your real needs. CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote
Re: [sqlite] Is this possible in SQLite?
> Is there a way to do the following: > > INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X ); > INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); http://www.sqlite.org/lang_insert.html sql-statement ::= INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list) | INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement Regards
[sqlite] Is this possible in SQLite?
Hello all, I have a fairly simple DB with two tables. I'm trying to combine a SELECT and UPDATE command, if it is possible: CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); ... And at some point in the future, two new tables (possibly in a different database) are created: CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); Is there a way to do the following: INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X ); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); Instead of doing the following 4 commands or SELECTing a and b into TEMP tables: UPDATE a SET id = X WHERE id = 1; UPDATE b SET id = X WHERE id = 1; INSERT INTO acopy SELECT * FROM a; INSERT INTO bcopy SELECT * FROM b; Thanks, John
