Re: [sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
Hi Ian, This one has been interesting! I'm trying to repeat the problem with several different databases - so far no luck. I use SQLite 2.8xx (project is already ongoing) and will test when I have time. I will post results if I find anything of value. At 06:41 PM 3/21/06 +, you wrote: > >On 21 Mar 2006, at 18:11, Ed Porter wrote: > >> Hi Ian, >> >> You nee to use a Full Outer Join. I don't know if SQLite has this >> function. >> >> > >Nah, definitely only wanted a left outer, didn't want the results >multiplied up or anything, just wanted every Category and any Cases >if matching the criteria. > >It was solved by moving the where clause up into an AND on the join, >seems that the where clause choking the result set from the outer >join. Never seen this before, but then again I've generally used "old >style" syntax joins (in the where clause), not ANSI, so I'm a bit new >to how the where clause can effect the results of an ANSI join. But >I'm learning! > >Thanks for your response though Ed, much appreciated. > >Regards, >-- >Ian M. Jones >___ >IMiJ Software >http://www.imijsoft.com >http://www.ianmjones.net (blog) > > > Sincerely, Ed Porter
Re: [sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
On 21 Mar 2006, at 18:11, Ed Porter wrote: Hi Ian, You nee to use a Full Outer Join. I don't know if SQLite has this function. Nah, definitely only wanted a left outer, didn't want the results multiplied up or anything, just wanted every Category and any Cases if matching the criteria. It was solved by moving the where clause up into an AND on the join, seems that the where clause choking the result set from the outer join. Never seen this before, but then again I've generally used "old style" syntax joins (in the where clause), not ANSI, so I'm a bit new to how the where clause can effect the results of an ANSI join. But I'm learning! Thanks for your response though Ed, much appreciated. Regards, -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
Re: [sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
Hi Ian, You nee to use a Full Outer Join. I don't know if SQLite has this function. At 03:23 PM 3/20/06 +, Ian M. Jones wrote: > >On 20 Mar 2006, at 15:14, Igor Tandetnik wrote: > >> Ian M. Jones wrote: >>> I'm trying to get a count of all Cases for each Category, with an >>> outer join to Cases so that I always get a record for each Category >>> regardless of whether there are any Cases with that Category or not. >>> >>> select xcat.Category, count(xc.CaseID) as NumCases >>> from Category as xcat >>> left join Cases as xc on xcat.CategoryID = xc.CategoryID >>> where xc.CaseID in (3145) >>> group by xcat.Category >>> order by xcat.Category >>> ; >> >> How many cases with an ID of 3145 do you expect to find? In other >> words, what's the purpose of the 'where' clause in your query? >> > >In the real query there is a need for the where clause, it's a way of >determining how many cases exist in each category for proper query >(the in clause contains a proper query). > >In this test example I'm just using CaseID 3145 to make sure that >only one category is matched, because I want to make sure the other >two categories still come back with a count of 0. > >But I'm not getting the other two categories coming back at all, let >alone with a count of 0. >-- >Ian M. Jones >___ >IMiJ Software >http://www.imijsoft.com >http://www.ianmjones.net (blog) > > > Sincerely, Ed Porter
Re: [sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
On 20 Mar 2006, at 15:14, Igor Tandetnik wrote: Ian M. Jones wrote: I'm trying to get a count of all Cases for each Category, with an outer join to Cases so that I always get a record for each Category regardless of whether there are any Cases with that Category or not. select xcat.Category, count(xc.CaseID) as NumCases from Category as xcat left join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) group by xcat.Category order by xcat.Category ; How many cases with an ID of 3145 do you expect to find? In other words, what's the purpose of the 'where' clause in your query? In the real query there is a need for the where clause, it's a way of determining how many cases exist in each category for proper query (the in clause contains a proper query). In this test example I'm just using CaseID 3145 to make sure that only one category is matched, because I want to make sure the other two categories still come back with a count of 0. But I'm not getting the other two categories coming back at all, let alone with a count of 0. -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
[sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
Ian M. Jones wrote: I'm trying to get a count of all Cases for each Category, with an outer join to Cases so that I always get a record for each Category regardless of whether there are any Cases with that Category or not. select xcat.Category, count(xc.CaseID) as NumCases from Category as xcat left join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) group by xcat.Category order by xcat.Category ; How many cases with an ID of 3145 do you expect to find? In other words, what's the purpose of the 'where' clause in your query? Igor Tandetnik
