List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 4 2011 4:21am
Subject:Re: Query query
View as plain text  
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 ::::
>
>
>
>
>
Thread
Query queryJan Steinman4 Dec
Re: Query queryJan Steinman4 Dec
  • Re: Query queryPeter Brawley4 Dec
Re: Query queryJan Steinman4 Dec
  • Re: Query queryArthur Fuller4 Dec
  • Re: Query queryhsv5 Dec
Re: Query queryJan Steinman7 Dec