[sqlite] LEFT OUTER JOIN + INNER JOIN problem

[Gábor Farkas]

hi,

consider the following sql:

CREATE TABLE "person" (
    "id" integer,
    "name" varchar
);
CREATE TABLE "role" (
    "id" integer,
    "name" varchar
);
CREATE TABLE "person_role" (
    "person_id" integer,
    "role_id" integer,
    UNIQUE ("person_id", "role_id")
);

insert into person values (1,'john');
insert into person values (2,'jack');

insert into role values (5,'admin');
insert into role values (6,'devel');

insert into person_role values (1,5);
insert into person_role values (2,6);

select * from role
LEFT OUTER JOIN person_role on role.id = person_role.role_id
INNER JOIN person on person_role.person_id = person.id
WHERE person.id=1;

the last select outputs:

5|admin|1|5|1|john
6|devel||||


i think it should only output the first row ( "5|admin|1|5|1|john" )

(at least postgres and mysql does it that way)..

so, am i doing something wrong here?

(using ubuntu edgy, sqlite3 3.3.5)

thanks a lot,
gabor farkas

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------