>On 25 March 2014 14:09, Christian Dallinger
><dallinger_christ...@hotmail.com> wrote:
>> Hello, I want to join these 3 tables from my database:
>>
>> http://pastebin.com/f8FP0G0C
>
>something like
>
>select Macadress, timestamp, result, user, '', '', '', '' from checklog
>union
>select MacAdress, timestamp, '', '', '', error1, error2, errortext from
>error;
>union
>select MacAdress, timestamp, '', '', text, '', '', '' from service
>order by timestamp
and if the same macaddress/timestamp can appear in more than one table and you
need to coalsce the results, then union the macadress and timestamp from all
the tables, then LEFT OUTER JOIN all the other columns from all the other
tables. Proper indexes a must for non-trivial volumes of data (more than a
dozen rows).
select B.MacAdress,
B.timestamp,
result,
user,
text,
error1,
error2,
errortext
from (select macaddress, timestamp
from checklog
UNION
select macaddress, timestamp
from service
UNION
select macaddress, timestamp
from error) as B
LEFT JOIN checklog as c on c.macaddress = b.macaddress and c.timestamp =
b.timestamp
LEFT JOIN service as s on s.macaddress = b.macaddress and s.timestamp =
b.timestamp
LEFT JOIN error as e on e.macaddress = b.macaddress and e.timestamp =
b.timestamp;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
