After some thinking I came up with this:
First, set up the example (note: no CTE as I am still runnning SQLIte 3.7.14.1):
CREATE temp TABLE stock(id, cid, sid);
CREATE temp TABLE clients(id,name);
CREATE temp TABLE suppliers(id,name);
insert into stock (id) values (1),(2),(3),(4),(5),(6),(7);
UPDATE stock SET cid = id WHERE id < 4;
UPDATE stock SET sid = id - 1 WHERE id BETWEEN 2 AND 5;
INSERT INTO clients SELECT cid, 'Hick'||cid FROM stock WHERE cid IS NOT
NULL;
INSERT INTO suppliers SELECT sid, 'Smith'||sid FROM stock WHERE sid IS NOT NULL;
The desired output contains rows that have a NULL client, so may as well create
a view for this (NOTE: renaming the columns to indicate the table they are
from):
create temp view clients_null as select id as cid,name as cname from clients
union all select null, null;
The same holds for rows that have a NULL supplier, so create a view for this
too (NOTE: renaming as above):
create temp view suppliers_null as select id as sid,name as sname from
suppliers union all select null, null;
Now we are ready to produce all possible rows, may as well do this with a view
too:
create temp view all_rows as select * from clients_null, suppliers_null;
And for the grand finale, join with stock to retrieve
1) stock connecting client to supplier
2) client with stock not connected to supplier
3) supplier with stock not connected to client
(NOTE: not a single LEFT or RIGHT join required)
(NOTE: if we could indicate that NULL == NULL should be true, just the first
expressions should suffice):
select cname,sname,id from all_rows a join stock s on ((a.sid = s.sid) and
(a.cid = s.cid)) or ((a.cid = s.cid) and (a.sid is null) and (s.sid is NULL))
or ((a.sid = s.sid) and (a.cid is null) and (s.cid is NULL));
cname|sname|id
Hick1|NULL|1
Hick2|Smith1|2
Hick3|Smith2|3
NULL|Smith3|4
NULL|Smith4|5
Adding a dummy value instead of NULL yields (NOTE: where clause to eliminate
totally unconnected stock items):
select cname,sname,id from all_rows a join stock s on (ifnull(a.sid,-1) =
ifnull(s.sid,-1)) and (ifnull(a.cid,-1) = ifnull(s.cid,-1)) where cname not
null or sname not null;
Pursuing this idea further leads to:
drop view clients_null;
drop view suppliers_null;
create temp view clients_null as select id as cid,name as cname from clients
union all select -1, null;
create temp view suppliers_null as select id as sid,name as sname from
suppliers union all select -1, null;
create temp view stock_dummy as select id,ifnull(sid,-1) as sid,ifnull(cid,-1)
as cid from stock;
and the quite readable statement:
select cname,sname,id from all_rows a join stock_dummy s on (a.sid = s.sid) and
(a.cid = s.cid) where cname not null or sname not null;
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [email protected]
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
