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 ::::
>
>
>
>
>
