[sqlite] Creating a view

[Gert Van Assche]

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