Yeah. USING is good to have :)
And yes, 123 might very well be a random number. I should have made that clear! > To: sqlite-users@sqlite.org > From: oliver....@web.de > Date: Sat, 25 Sep 2010 00:19:44 +0000 > Subject: Re: [sqlite] Need help with self-join (I think) > > Kristoffer Danielsson <kristoffer.daniels...@...> writes: > > > > > > > Ah, this one's easier to follow. What do you mean by "in this case"? What > > was > the condition that made it > > the point was not to remove something but to take into consideration that > there > has to be a JOIN on the TestIDs (in this case results doesn't differ because > you > only have 123 as SomeValue) - and because it's too late/early here is my > assumed > last correction (see the USING I forgot, if you forget this you will get a > Cartesian Product) > > SELECT DISTINCT t.Year, b.Name, > (SELECT SomeValue FROM Test INNER JOIN Test2 USING(TestID) WHERE Year = t.Year > AND Name = > b.Name) AS SomeValue > FROM Test t > CROSS JOIN (SELECT DISTINCT Name FROM Test) b > ; > > > > > > possible to remove the rest? > > > > Again, thanks! > > > > > > > To: sqlite-us...@... > > > From: oliver....@... > > > Date: Sat, 25 Sep 2010 00:05:11 +0000 > > > Subject: Re: [sqlite] Need help with self-join (I think) > > > > > > sry, > > > > > > a little mistake - here's the correction: > > > > > > SELECT DISTINCT t.Year, b.Name, > > > (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name > > > = > > > b.Name) AS SomeValue > > > FROM Test t > > > CROSS JOIN (SELECT DISTINCT Name FROM Test) b > > > ; > > > > > > > > > (results don't differ - in this case!) > > > > > > Oliver > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-us...@... > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-us...@... > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
