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
