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;
Many thanks Igor Apart from a '.' where a ',' should have been it's working a treat Cheers Walter _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
