Actually, had I read your (second?) email properly, I would've suggested something slightly different. (I guess, to get a 0, rather than count(*) it should've been a count(startyear)).
Instead of a LEFT JOIN, which will create NULL rows for any years that don't have a project, do a straight JOIN using just a where clause. SELECT y.Year, count(*) FROM Year as y , projects as p WHERE p.startyear<=y.Year AND p.endyear>=y.Year GROUP BY y.Year; A simple change, but that should mean it will only list years that have projects falling across them. Try that? Good luck =] Nick Elliott ----- Original Message ----- From: "Steve Vernon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 02, 2003 8:53 AM Subject: Re: Year Lists > Thanks for the email... > > Brilliant idea, but it does not give 0 for rows without a project for a > year. It gives 1 for these rows. > > As a test idea, I removed the group by and count, and instead of count > outputted the start and end year. > > What that gave me was say for 1953 where there was 3 projects, 3 lines with > the start and end year for those projects. But for a year without a project > gives start and end year as null. > > e.g. (not true examle) > > year startyear endyear > 1901 NULL NULL > .... > 1953 1952 1954 > 1953 1951 1956 > 1953 1950 1980 > .... > > > I suppose I need to do a where to remove null values, but I cant work > out where it goes! > Thanks, ;-) > > Steve > XX > > > > Sounds to me like you'll need to join for that -- and list all the years > you > > are interested in in another table. Try this? > > > > SELECT y.Year, count(*) FROM Year as y LEFT JOIN projects ON > > project.startyear<=y.Year AND project.endyear>=y.Year GROUP BY y.Year; > > > > This will give you a count of 0 for any years without a project (Almost > 100% > > certain =] ). But don't blame me if the query is grossly inefficient! > > > > Nick Elliott > > > > ----- Original Message ----- > > From: "Steve Vernon" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Tuesday, December 31, 2002 10:44 AM > > Subject: Re: Year Lists > > > > > > > Martin, > > > Thanks for the reply. > > > > > > Good plan but at the moment my query is like (had to alter it > slightly > > > as it was in PHP and multiple lines, so dont take it as working). > > > > > > SELECT projects.name, project.endyear, project.startyear FROM projects > > WHERE > > > projects.startyear <= '(INPUT THE YEAR HERE) && (projects.leaveyear >= > > > '(YEAR HERE AS WELL)' || projects.leaveyear = '0000' ) LIMIT 5000 > > > > > > What you suggest basically will give a count on start year (or end > > year > > > if altered), which I have implemented and working for ended year. Say a > > > project started in 1920 and another in 1921 and they both finish in > 1925. > > I > > > would expect to get this: > > > > > > 1920 1 Project > > > 1921 2 Projects > > > 1922 2 Projects > > > 1923 2 Projects > > > 1924 2 Projects > > > 1925 2 Projects > > > 1926 0 Projects (or no line at all ideally). > > > .... up to 2003 > > > > > > > > > > > > Ideally if a year does not have any projects it would not be listed. > > > > > > Any more help would be great, or I could be wrong and you could have > > the > > > answer! > > > > > > Steve > > > > > > > > > > > > > On Tue, 2002-12-31 at 14:53, Steve Vernon wrote: > > > > > Hiya, > > > > > I have a database about projects in a company, they all have a > > start > > > > > year and end year. If the projects have not ended then they get a > end > > > year > > > > > of 0000. I have made a SQL command, given a year, works out with > > > projects > > > > > are running in that year. > > > > > > > > > > The company I am doing this for, wants basically a drop down box > > > which > > > > > says (1950- 8 Projects) and such like. Now with the current command > I > > > have > > > > > it would mean 92or so SQL commands as the company has records back > to > > > 1910. > > > > > > > > > > Is there a way to do this in one command? I have searched all > the > > > > > mannual and I can work out something similair to a for loop in SQL. > > > Would > > > > > variables help? > > > > > > > > > > > > > Hi Steve, > > > > > > > > You want to use "select .... group by", something like: > > > > > > > > select start_year, count(*) from projects group by start_year; > > > > > > > > > > > > == > > > > Martin > > > > > > > > > > > > --------------------------------------------------------------------- > > > > Before posting, please check: > > > > http://www.mysql.com/manual.php (the manual) > > > > http://lists.mysql.com/ (the list archive) > > > > > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > > > > --------------------------------------------------------------------- > > > Before posting, please check: > > > http://www.mysql.com/manual.php (the manual) > > > http://lists.mysql.com/ (the list archive) > > > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
