thanks Pavel,
and sorry for mixing the wikipedia example with the real situation.
create table RT1 ( PID integer, V1 text );
insert into RT1 values ( '684', 'aap' );
insert into RT1 values ( '685', 'other empty' );
create table RT2 ( PID integer, V2 text );
insert into RT2 values ( '684', 'beer' );
insert into RT2 values ( '686', 'other empty' );
select RT1.*, RT2.*
from RT1
left join RT2 on RT1.PID = RT2.PID
union
select RT1.*, RT2.*
from RT2
left join RT1 on RT1.PID = RT2.PID
where RT1.PID IS NULL;
ok I tried in sqlite3 command line ( terrible job ;-)
and as you said, both "union" and "union all" works correct (only a
different order).
And I also can add the "order by" clause without affecting the result
(except ordening).
So the problem is in the IDE I use.
I tried another IDE which worked also correct.
Now I still have one question:
The result of the above query is:
PID V1 PID V2
686 from RT2
684 from RT1 684 from RT2
685 from RT1
Now I want to combine the columns PID, so the result would look like
PID V1 V2
686 from RT2
684 from RT1 from RT2
685 from RT1
Is that possible with SQL ?
cheers,
Stef
So let's try again:
This following code in the sqlite command line utility:
create table RT1 ( PID integer, V1 text );
insert into RT1 values ( '684', 'aap' );
insert into RT1 values ( '685', 'other empty' );
create table RT2 ( PID integer, V2 text );
insert into RT2 values ( '684', 'beer' );
insert into RT2 values ( '686', 'other empty' );
select RT1.*, RT2.*
from RT1
left join RT2 on RT1.PID = RT2.PID
union
select RT1.*, RT2.*
from RT2
left join RT1 on RT1.PID = RT2.PID
where RT1.PID IS NULL;
gives the following output ( I hope I don't make typo's because I don't
know how to copy from the command line window)
684|from RT1|684|from RT2
685|from RT1||
||684|from RT2
which is as (I) expected.
If "union all" is replaced by
Pavel Ivanov wrote:
>> Now the strange thing is that this query returns the correct number of rows,
>> but all the columns from the employee-table are empty.
>>
>
> There's no "employee-table" in your query.
>
>
>> If I change "UNION" to "UNION ALL" the join works as expected.
>> Is there an explanation for this behavior ?
>>
>
> As we don't see your query we can't explain what's going on there.
>
>
>> Also when I add an order clause, the left columns are all made empty ?
>>
>
> Justing adding ORDER BY changes all "left" (from what?) columns in all
> rows to NULL? No way! Show the actual output from sqlite3 command line
> utility please.
>
>
>> Is it uberhaupt possible to order the resulting table on the column PID
>> and preferable get just 1 PID column ?
>>
>
> Sure, why not? But again no query - no advice on how to correct it to
> achieve what you want.
>
> Pavel
>
> On Thu, Sep 17, 2009 at 6:39 PM, Stef Mientki <s.mien...@ru.nl> wrote:
>
>> hello,
>>
>> I'm trying to join 2 tables,
>> so I guess I need to perform a full outer join.
>>
>> On wikipedia, I found this solution for sqlite3:
>> http://en.wikipedia.org/wiki/Join_%28SQL%29
>>
>> select *
>> from RT0
>> left join RT1 on RT1.PID = RT0.PID
>> union
>> select RT0.*, RT1.*
>> from RT1
>> left join RT0 on RT1.PID = RT0.PID
>> where RT0.PID IS NULL
>>
>> Now the strange thing is that this query returns the correct number of rows,
>> but all the columns from the employee-table are empty.
>>
>> If I change "UNION" to "UNION ALL" the join works as expected.
>> Is there an explanation for this behavior ?
>>
>> Also when I add an order clause, the left columns are all made empty ?
>>
>> Is it uberhaupt possible to order the resulting table on the column PID
>> and preferable get just 1 PID column ?
>>
>> thanks,
>> Stef
>> _______________________________________________
>> 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
