Re: [sqlite] Creating a view
Thanks, Igor & Keith. gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating a view
I believe that unless you have constrained nR1 nR2 nR3 ... nR6 as NOT NULL then
select
(select RefItem from REFTABLE where id=nR1),
(select RefItem from REFTABLE where id=nR2),
(select RefItem from REFTABLE where id=nR3),
(select RefItem from REFTABLE where id=nR4),
(select RefItem from REFTABLE where id=nR5),
(select RefItem from REFTABLE where id=nR6)
from TBL;
written in join syntax needs to be expressed thusly:
select ref1.RetItem, ref2.RetItem, ref3.RetItem, ref4.RetItem,
ref5.RetItem, ref6.RetItem
from TBL
outer join REFTABLE ref1 on (nR1=ref1.id)
outer join REFTABLE ref2 on (nR2=ref2.id)
outer join REFTABLE ref3 on (nR3=ref3.id)
outer join REFTABLE ref4 on (nR4=ref4.id)
outer join REFTABLE ref5 on (nR5=ref5.id)
outer join REFTABLE ref6 on (nR6=ref6.id);
in order to obtain the comparable result sets where there is a NULL value.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> On 10/17/2012 4:23 PM, Gert Van Assche wrote:
> > I don't know how to do something very simple like this. I have two table
> > and I would like to see the value of one table as it is expressed in the
> > other.
> >
> > CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> > [RefItem] CHAR);
> > INSERT INTO [REFTABLE]([RefItem]) VALUES('One');
> > INSERT INTO [REFTABLE]([RefItem]) VALUES('Two');
> > INSERT INTO [REFTABLE]([RefItem]) VALUES('Three');
> >
> > CREATE TABLE [TBL] (
> >id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> >[nR1] CONSTRAINT [r1] REFERENCES [REFTABLE]([id]),
> >[nR2] CONSTRAINT [r2] REFERENCES [REFTABLE]([id]),
> >[nR3] CONSTRAINT [r3] REFERENCES [REFTABLE]([id]),
> >[nR4] CONSTRAINT [r4] REFERENCES [REFTABLE]([id]),
> >[nR5] CONSTRAINT [r5] REFERENCES [REFTABLE]([id]),
> >[nR6] CONSTRAINT [r6] REFERENCES [REFTABLE]([id])
> >);
> >
> > INSERT INTO [TBL]([nR1], [nR2], [nR3], [nR4], [nR5], [nR6]) VALUES('1',
> > '2', '2', '3', '3', '3');
> >
> > I would like to do a select from TBL where I would see this:
> > 'One', 'Two', 'Two', 'Three', 'Three', 'Three'
>
> select
>(select RefItem from REFTABLE where id=nR1),
>(select RefItem from REFTABLE where id=nR2),
>(select RefItem from REFTABLE where id=nR3),
>(select RefItem from REFTABLE where id=nR4),
>(select RefItem from REFTABLE where id=nR5),
>(select RefItem from REFTABLE where id=nR6)
> from TBL;
>
> -- or
>
> select ref1.RetItem, ref2.RetItem, ref3.RetItem, ref4.RetItem,
> ref5.RetItem, ref6.RetItem
> from TBL join REFTABLE ref1 on (nR1=ref1.id)
>join REFTABLE ref2 on (nR2=ref2.id)
>join REFTABLE ref3 on (nR3=ref3.id)
>join REFTABLE ref4 on (nR4=ref4.id)
>join REFTABLE ref5 on (nR5=ref5.id)
>join REFTABLE ref6 on (nR6=ref6.id);
>
> --
> Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating a view
On 10/17/2012 4:23 PM, Gert Van Assche wrote:
I don't know how to do something very simple like this. I have two table
and I would like to see the value of one table as it is expressed in the
other.
CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[RefItem] CHAR);
INSERT INTO [REFTABLE]([RefItem]) VALUES('One');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Two');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Three');
CREATE TABLE [TBL] (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[nR1] CONSTRAINT [r1] REFERENCES [REFTABLE]([id]),
[nR2] CONSTRAINT [r2] REFERENCES [REFTABLE]([id]),
[nR3] CONSTRAINT [r3] REFERENCES [REFTABLE]([id]),
[nR4] CONSTRAINT [r4] REFERENCES [REFTABLE]([id]),
[nR5] CONSTRAINT [r5] REFERENCES [REFTABLE]([id]),
[nR6] CONSTRAINT [r6] REFERENCES [REFTABLE]([id])
);
INSERT INTO [TBL]([nR1], [nR2], [nR3], [nR4], [nR5], [nR6]) VALUES('1',
'2', '2', '3', '3', '3');
I would like to do a select from TBL where I would see this:
'One', 'Two', 'Two', 'Three', 'Three', 'Three'
select
(select RefItem from REFTABLE where id=nR1),
(select RefItem from REFTABLE where id=nR2),
(select RefItem from REFTABLE where id=nR3),
(select RefItem from REFTABLE where id=nR4),
(select RefItem from REFTABLE where id=nR5),
(select RefItem from REFTABLE where id=nR6)
from TBL;
-- or
select ref1.RetItem, ref2.RetItem, ref3.RetItem, ref4.RetItem,
ref5.RetItem, ref6.RetItem
from TBL join REFTABLE ref1 on (nR1=ref1.id)
join REFTABLE ref2 on (nR2=ref2.id)
join REFTABLE ref3 on (nR3=ref3.id)
join REFTABLE ref4 on (nR4=ref4.id)
join REFTABLE ref5 on (nR5=ref5.id)
join REFTABLE ref6 on (nR6=ref6.id);
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Creating a view
All,
I don't know how to do something very simple like this. I have two table
and I would like to see the value of one table as it is expressed in the
other.
CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[RefItem] CHAR);
INSERT INTO [REFTABLE]([RefItem]) VALUES('One');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Two');
INSERT INTO [REFTABLE]([RefItem]) VALUES('Three');
CREATE TABLE [TBL] (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[nR1] CONSTRAINT [r1] REFERENCES [REFTABLE]([id]),
[nR2] CONSTRAINT [r2] REFERENCES [REFTABLE]([id]),
[nR3] CONSTRAINT [r3] REFERENCES [REFTABLE]([id]),
[nR4] CONSTRAINT [r4] REFERENCES [REFTABLE]([id]),
[nR5] CONSTRAINT [r5] REFERENCES [REFTABLE]([id]),
[nR6] CONSTRAINT [r6] REFERENCES [REFTABLE]([id])
);
INSERT INTO [TBL]([nR1], [nR2], [nR3], [nR4], [nR5], [nR6]) VALUES('1',
'2', '2', '3', '3', '3');
I would like to do a select from TBL where I would see this:
'One', 'Two', 'Two', 'Three', 'Three', 'Three'
How should I do this? If there is just one field, I can use a JOIN, but
with several fields I don't see how to do this. Maybe I'm also doing this
completely wrong...
thanks
g.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating a view on an ATTACHed database
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote: > > Out of curiosity though, is there a reason why ... a view > [across multiple attached databases] can't be stored permanently? When you open a database and first try to use it, SQLite scans the SQLITE_MASTER table and parses the schema. Views are stored in sqlite_master like all other parts of the schema. IIRC, the parser would get upset if it tried to parse a view that referenced a table that did not yet exist. For that reason, a view cannot reference a table in a different database. I might have fixed the parser at some point so that it will accept a view definition that includes undefined tables, but the restriction on views not referencing tables in other databases seems a reasonable restriction so I left that in. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating a view on an ATTACHed database
From: <[EMAIL PROTECTED]> "Jeff Godfrey" <[EMAIL PROTECTED]> wrote: So, is it not possible to create a view across a "main" and an "attached" database? If I recall, you can create a TEMP VIEW across attached databases. Thanks for the tip. Adding TEMP is all it took to get things working. Out of curiosity though, is there a reason why such a view can't be stored permanently? Obviously, it can't be "used" until the other table(s) are attached, but being able to store it would seem to make things a bit tidier... I may be way off base - just wondering... Again - thanks. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating a view on an ATTACHed database
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote: > > So, is it not possible to create a view across a "main" and > an "attached" database? > If I recall, you can create a TEMP VIEW across attached databases. But you can't create a persistent view because such a view would not make sense to a processes that opened only one database without opening the other attached databases. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Creating a view on an ATTACHed database
Hi All, I have an open sqlite3 database (name = dbSerial), to which I've attached a 2nd database (name = dbParent). Now, I'm trying to create a view by joining a view from dbSerial with another view from dbParent. Attempting to create the view generates the following error: Error: view [tcoverage] cannot reference objects in database dbParent Specifically, here's my (contrived) view creation code... SELECT c.zone, t.zone FROM precoverage AS c INNER JOIN dbParent.target AS t ON (c.zone = t.zone) So, is it not possible to create a view across a "main" and an "attached" database? If not, what's my best option (copy the necessary data to a single (in memory?) database?)... Thanks for any pointers. Jeff
[sqlite] creating a view on an attached database
I am attaching external database 'b' to database 'a', ATTACH DATABASE b AS d_b; and then trying to create a view that uses tables from the attached database, CREATE VIEW v AS SELECT d_b_t.col, d_a_t.col FROM d_b.tbl AS d_b_t JOIN tbl AS d_a_t ON... I get the following error -- view v cannot reference objects in database d_b(1) is that not allowed? -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
