Hi,
Thanks for your reply. You're right, it's an outer-join I'm looking for. Unfortunately, your suggestion does not do the trick. "You didn't have a Test1 row for (2007, 'C'), so why would you get 2007|C|NULL?" Because, given a certain algorithm, generating statistics will become a lot easier if each value combination is represented in the returned row set. Perhaps a UNION is needed for this type of query? > Date: Fri, 24 Sep 2010 18:17:51 -0500 > From: [email protected] > To: [email protected] > Subject: Re: [sqlite] Need help with self-join (I think) > > On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote: > > 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); > > > > [...] > > 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 > > You didn't have a Test1 row for (2007, 'C'), so why would you get > 2007|C|NULL? You also had one (and just one) row in Test2 for every > TestID in Test, so there are no NULLs that could appear as you request. > > But, if you did: > > INSERT INTO Test (Year, Name) VALUES (2007, 'C'); > > without a corresponding row in Test2, then your SELECT would not return > 2007|C|NULL. Try this: > > SELECT Year, Name, SomeValue > FROM Test LEFT OUTER JOIN Test2 ON (Test.TestID = Test2.TestID) > ORDER BY Year, Name; > > (Self-join is when both sides of the JOIN use the same table. That's > not the case here. What you were looking for here is an OUTER JOIN > instead of an INNER JOIN; NATURAL JOINs are a type of INNER JOIN.) > > Nico > -- > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
