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...
SELECT
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
> (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
