Walter <rick...@iinet.net.au> wrote: > I have the following tables > > CREATE TABLE data( > id INTEGER PRIMARY KEY NOT NULL, > name TEXT DEFAULT '', > title TEXT DEFAULT '', > .... > ) > > > CREATE TABLE pivot( > id INTEGER PRIMARY KEY NOT NULL, > link1 INTEGER DEFAULT 0, > link2 INTEGER DEFAULT 0, > rank INTEGER DEFAULT 0, > mdate MYDATE DEFAULT 0, > status TEXT DEFAULT '', > ) > > link1 and link2 are id's from tbl1 > > With this sql I get half way to what I want > > SELECT name,title,pivot.id AS id,mdate,status > FROM data,pivot > WHERE data.id=pivot.link1 > ORDER BY name,pivot.rank > > How do I get the name,title from data onto the same row > > WHERE data.id=pivot.link1 and pivot.link2=a different data.id > this obviously does not work
select d1.name, d1.title, d2.name. d2.title, pivot.id, mdate, status from pivot join data d1 on (pivot.link1 = d1.id) join data d2 on (pivot.link2 = d2.id) order by d1.name, pivot.rank; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
