Makes sense. Thanks.
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, May 25, 2011 9:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Incorrect left join behavior?
> I can use two left joins. While writing the first left join, I discovered
> that it is behaving like an inner join.
>
> select *
> from test a
> left join test b on a.component = b.component
> where a.machine = 'machine1'
> and b.machine = 'machine2';
By the WHERE condition you limit results of your left join only to
those that have b.machine equal to 'machine2' and exclude other 2 rows
where b.machine is null. To get the result you need you have to
rewrite the query in one of the following ways:
select *
from test a left join test b
on a.component = b.component
and b.machine = 'machine2'
where a.machine = 'machine1';
or
select *
from test a left join test b
on a.component = b.component
where a.machine = 'machine1'
and (b.machine is null or b.machine = 'machine2');
I think the first syntax is preferable.
Pavel
On Wed, May 25, 2011 at 9:09 AM, Michael Stephenson
<domehead...@gmail.com> wrote:
> Hi, I have a table as below. Note that machine1 has 3 components (1-3),
> while machine2 has only 1 components (1).
>
>
>
>
> Machine
>
> Component
>
> Version
>
>
> machine1
>
> component1
>
> 1
>
>
> machine1
>
> component2
>
> 1
>
>
> machine1
>
> component3
>
> 1
>
>
> machine2
>
> component1
>
> 1
>
>
>
> create table test(Machine, Component, Version);
>
> insert into test values('machine1', 'component1', 1);
>
> insert into test values('machine1', 'component2', 1);
>
> insert into test values('machine1', 'component3', 1);
>
> insert into test values('machine2', 'component1', 1);
>
>
>
> I need to see what component versions are different on the machines. What
I
> really need is a "self outer join", but since SQLite doesn't do outer
joins,
> I can use two left joins. While writing the first left join, I discovered
> that it is behaving like an inner join.
>
>
>
> select *
>
> from test a
>
> left join test b on a.component = b.component
>
> where a.machine = 'machine1'
>
> and b.machine = 'machine2';
>
>
>
>
>
> The expected behavior would be to return 3 rows: one row showing the
> component versions for machine1 and machine2, and two rows showing the
> component version for machine1 with null for machine2.
>
>
>
> The actual behavior is that of an inner join: I get only one row that
shows
> the component versions for both machines, and the other two components,
> which don't exist for machine2, do not appear in the results at all.
>
>
>
> I'm aware that SQLite only does left joins properly using SQL92 syntax,
> which I am using. I'm also aware that I'm using a where clause, and the
> docs state that join constraints in a where clause cause a left join to
> behave as an inner join. However, my where clause does not constrain the
> join expression, just the initial rows involved available to be joined.
>
>
>
> I'm able to work around this by:
>
>
>
> select *
>
> from (select * from test where machine = 'machine1') a
>
> (select * from test where machine = 'machine2') b
>
> on a.component = b.component;
>
>
>
> But, I just wanted to point this behavior out. Perhaps I've missed
> something. It looks like any time there is a where clause, a left join is
> going to behave as an inner join.
>
>
>
> Thanks,
>
>
>
> ~Mike
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
