On 12/3/2011 9:35 PM, Jan Steinman wrote:
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
Lose WHERE 1, it's useless. Add GROUP BY ddn.Dewey to get all counts.
PB
-----
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