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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users