Hi,
I have an interesting SQL problem where I want certain rows to always be
represented. It feels like a self-join, but I'm not sure. Please help!
Create a database as follows:
************************************************************************
CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT NOT
NULL);
INSERT INTO Test (Year, Name) VALUES (2007, 'A');
INSERT INTO Test (Year, Name) VALUES (2007, 'B');
INSERT INTO Test (Year, Name) VALUES (2008, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'A');
INSERT INTO Test (Year, Name) VALUES (2009, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'C');
CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);
INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);
************************************************************************
SELECT Year, Name, SomeValue FROM Test NATURAL JOIN Test2 ORDER BY Year, Name;
2007|A|123
2007|B|123
2008|B|123
2009|A|123
2009|B|123
2009|C|123
My problem is that I want each combination of Year+Name to be represented -
with zero/NULL for SomeValue. How do I accomplish this?
2007|A|123
2007|B|123
2007|C|NULL
2008|A|NULL
2008|B|123
2008|C|NULL
2009|A|123
2009|B|123
2009|C|123
If there is an "easy" solution it would be great, as the original query is
quite complex... :P
Thank you for your help!
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
