List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:December 4 2011 2:59am
Subject:Query query
View as plain text  
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