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