From: Jan Steinman
Date: December 4 2011 3:35am
Subject: Re: Query query
List-Archive: http://lists.mysql.com/mysql/226436
Message-Id: <3B8FF375-CF73-40A0-8C69-1C535B17D86D@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

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 =3D FLOOR(lib.Dewey)
WHERE 1


> I'm having brain freeze, and wonder if anyone can help me with a =
query.
>=20
> 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.
>=20
> 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.
>=20
> 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?)
>=20
> 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.
>=20
> SELECT
>    (SELECT COUNT(*) > 0 FROM s_library lib WHERE FLOOR(lib.Dewey) =3D =
ddn.Dewey) AS Have,
>    ddn.Dewey AS DDN,
>    ddn.Classification AS Classification
> FROM s_library_dewey
> ddn WHERE 1
>=20
> Any thoughts on the best way to do this?
>=20
> Thanks!
>=20
----------------
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 ::::