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
