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
(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?
Thanks!
----------------
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 ::::
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
