NYCPHP Meetup

[Tim Gales]

Christopher R. Merlo writes:

"Each row has a date,  student ID, and in column "attended", a value of 1,
2, or 3"

The name of the column attended suggest it should 
have only two values.

If you only had two possible values, you would technically 
be doing 'two valued first order predicate calculus' -- 
the fancy name for Boolean algebra -- something that data-
bases are well geared for (well they can handle anyway)

(since there is a difference between theory and practice 
I 'll skip the math and give you a suggestion)

alter the table and add a column absent (which could 
be excused or unexcused).

you can see that there is a parallel between two columns 
which could have a 0 or 1 and the binary representation of
three values with  01 10 and 11. 

(you're sort of synthesizing four-valued first order 
predicate algebra -- yes four -- '00' represents that pesky null)

Let me know if you can make heads or tails
of this post.


T. Gales & Associates
'Helping People Connect with Technology'

http://www.tgaconnect.com


>From hans not junk at nyphp.com  Mon Feb  9 20:08:48 2004
Return-Path: <hans not junk at nyphp.com>
Received: from ehost011-1.exch011.intermedia.net (unknown [64.78.21.3])
	by virtu.nyphp.org (Postfix) with ESMTP id 308DFA8602
	for <talk at lists.nyphp.org>; Mon,  9 Feb 2004 20:08:48 -0500 (EST)
X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: RE: [nycphp-talk] MySQL count( * ) syntax question
Date: Mon, 9 Feb 2004 17:08:47 -0800
Message-ID: <41EE526EC2D3C74286415780D3BA9F87772520 at ehost011-1.exch011.intermedia.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [nycphp-talk] MySQL count( * ) syntax question
Thread-Index: AcPvZamqh6Tj50zpTaGODs7NQC0SeQAB/P8Q
From: "Hans Zaunere" <hans not junk at nyphp.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
X-BeenThere: talk at lists.nyphp.org
X-Mailman-Version: 2.1.2
Precedence: list
Reply-To: NYPHP Talk <talk at lists.nyphp.org>
List-Id: NYPHP Talk  <talk.lists.nyphp.org>
List-Unsubscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
	<mailto:talk-request at lists.nyphp.org?subject=unsubscribe>
List-Archive: <http://lists.nyphp.org/pipermail/talk>
List-Post: <mailto:talk at lists.nyphp.org>
List-Help: <mailto:talk-request at lists.nyphp.org?subject=help>
List-Subscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
	<mailto:talk-request at lists.nyphp.org?subject=subscribe>
X-List-Received-Date: Tue, 10 Feb 2004 01:08:48 -0000


> I'm running a query that has a count( column_name ) statement in it.
> The query only returns a row if the count is positive.  Is there any
> way to force MySQL to also generate a row if the count is zero?
> MySQL's docs on the subject don't seem to say anything (probably
> because this is a weird request, and I need to rethink my query).

I think there are two issues here.  For one, using COUNT() will return a row, containing a count of zero.

select count(user) from user where user='not.a.user'

+-------------+
| count(user) |
+-------------+
|           0 |
+-------------+

But from seeing your query in followup posts, I think you might want something like:

SELECT id,first,last,COUNT(attended)
FROM login
   LEFT JOIN attendance ON attendance.student=login.id AND attendance.attended=2
GROUP BY id

This is one of the finer, and more annoying "features" of mysql (and I'm still not sure that'll work).

H