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
