DOH! Brain unfroze, and I realized I needed an aggregate:
COUNT(lib.Dewey) AS Have,
ddn.Dewey AS DDN,
ddn.Classification AS Classification
FROM s_library_dewey ddn
LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey)
GROUP BY ddn.Dewey
... although if there are any brighter ideas, I'm all ears. This doesn't seem very optimal
to me, with the FLOOR() function in the JOIN and all... takes over half a second...
> Second attempt, using a join, returns just one row for Dewey "000" with the COUNT
> being about half the volumes in the library, which isn't right...
> I thought a LEFT OUTER JOIN would have returned a record for every record in
> s_library_dewey, but it only returns the first.
> Brain freeze again...
> COUNT(lib.Dewey) AS Have,
> ddn.Dewey AS DDN,
> ddn.Classification AS Classification
> FROM s_library_dewey ddn
> LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey)
> WHERE 1
>> I'm having brain freeze, and wonder if anyone can help me with a query.
>> I have a library in MySQL. There's a table with a record per book, and other
> tables that it indexes into for meaningful info. One of those is an integer-keyed list of
> 1,000 Dewey Decimal Codes. In the books table, the Dewey field is decimal. In the Dewey
> table, it's an integer.
>> I would like to make a report with the info for each DDC, including whether or
> not there are any books for any given code's integer part. In other words, I want to
> "bucketize" 101.000 to 101.999, etc, for each integer Dewey number, and give some info if
> the count in that range is non-zero.
>> I suspect I need a subquery to do this, but my brain is frozen! (Or should I use
> a join? Can you even join on an inequality?)
>> The following crashes phpMyAdmin when I try to do it. I suspect it's because the
> subquery reference to "ddn.Dewey" is out of context. The subquery works on its own when
> "ddn.Dewey" is a literal integer.
>> (SELECT COUNT(*) > 0 FROM s_library lib WHERE FLOOR(lib.Dewey) = ddn.Dewey)
> AS Have,
>> ddn.Dewey AS DDN,
>> ddn.Classification AS Classification
>> FROM s_library_dewey
>> ddn WHERE 1
>> Any thoughts on the best way to do this?
After providing the wealth on which the city is built, the countryside and its people are
increasingly seen as dispensable. When it appears that cities can thrive on their global
connections, rural hinterlands die. -- David Holmgren
:::: Jan Steinman, EcoReality Co-op ::::