With the help of the good folks in this group I have this query working
with the table shown.

Query:

$query = "select event.eventID, UNIX_TIMESTAMP(eventName) AS unixdate,
typeName from event\n"
. "left join xm2e on event.eventID = xm2e.eventID\n"
. "and xm2e.memberID = $member\n"
. "inner join type on type.typeID = event.typeID where eventName >=
NOW() group by eventID\n"
. "having count(xm2e.memberID) = 0\n"
. "ORDER BY eventName";


Table:

-- Table structure for table `xm2e`
--

DROP TABLE IF EXISTS `xm2e`;
CREATE TABLE IF NOT EXISTS `xm2e` (
`xID` int(11) NOT NULL auto_increment,
`memberID` int(11) NOT NULL,
`eventID` int(11) NOT NULL,
PRIMARY KEY (`xID`),
UNIQUE KEY `memberID` (`memberID`,`eventID`),
UNIQUE KEY `eventID` (`eventID`,`memberID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=130 ;

The query finds all events where the member is not signed up to sing, the
script then goes on to print a form of check-boxes to sign up and the
form is processed to add new entries to the table.

So far, so good. It works well.

HOWEVER I've now been asked to provide a facility for saying 'sorry,
can't do that' so there are three states: signed up; no-can-do; and not
yet replied.

I am trying the idea of adding a fourth field to xm2e called 'noCanDo'.
'memberID' redefined to allow NULL. So each eventID entry has the
memberID either under memberID or noCanDo, the other field being NULL.

What I want to do is modify the query so that it only lists events that
have no entries for the member in either the memberID or noCanDo columns.


So far I've tried this:

$query = "select event.eventID, UNIX_TIMESTAMP(eventName) AS unixdate,
typeName from event\n"
. "left join xm2e on event.eventID = xm2e.eventID\n"
. "and (xm2e.memberID = $member OR xm2e.noCanDo = $member)\n"
. "inner join type on type.typeID = event.typeID where eventName >=
NOW() group by eventID\n"
. "having (count(xm2e.memberID) = 0 AND count(xm2e.noCanDo) = 0)\n"
. "ORDER BY eventName";

which half-works in that only the first of the 'noCanDo's is eliminated
from the returned list.

What have I missed?

On Thu, 11 Mar 2010 15:41:21 +0000, Derek Turner wrote:

Edit: no it doesn't even half-work, I was wrong, it doesn't work at all
for the 'noCanDo's

Derek Turner:


Either I don't exactly understand what the function of this "noCanDo"
status is, or I believe this approach is not the most elegant way to
solve it.

That said, let's see:

Do you really mean "either" here, or rather "and"?
It looks like you're looking for records that have both fields empty.


If I understand correctly, you want:

SELECT event.eventID,
UNIX_TIMESTAMP(eventName), // ????
typeName
FROM event
INNER JOIN type ON
type.typeID = event.typeID
LEFT JOIN xm2e ON
event.eventID = xm2e.eventID AND
(xm2e.memberID = $member OR xm2e.noCanDo = $member)

WHERE eventName >= NOW() // What does a Name have to do with NOW()?

GROUP BY eventID
HAVING count(xm2e.xID)=0



--
Erick

On Fri, 12 Mar 2010 07:26:36 +0000, Erick T. Barkhuis wrote:


Can't help but agree with you there it's a cludge but it's a cludge
that keeps all the info in one table. A more elegant solution would be a
separate xNoCanDo table, I guess.
Yes. That is the intended behaviour. The script should print out a list
of events that the member has not yet indicated 'yes' or 'no' to, and
give them the opportunity to choose yes, no, or leave it at 'maybe'.
eventName has type DATETIME. Not having the gift of bi-location we can't
be in two places at once - it's a unique property that I use to identify
the event. Why not eventTime? a suite of scripts allowing web-based
maintenance of the database requires xxxxID in the first column and
xxxxName in the second.

Derek Turner:


But Derek, if an Event record already has xxxxID to identify it, why
must the name be unique?
And can't there ever be two or more events called "Boston's Choir In
Concert"?

I would expect an Event record to look like this:

ID Name StartDateTime Duration LocationID TicketPrice
42 Choir Concert 2010-03-10 20:00 240 LOC14 12.50

[or similar]


--
Erick

On Fri, 12 Mar 2010 07:26:36 +0000, Erick T. Barkhuis wrote:


No what I'm trying to do is list the events where eventID is not present
under either eventID or noCanDo against that memberID. noCanDo contains
eventID's - those of the events the member has said 'no' to.

It's a list of events to which the member has said neither 'yes' nor
'no'. IOW I'm looking for non-existent rows in xm2e, or rather rows in
event where there is no entry in xm2e for that choir member.

I hope that makes it clearer

Derek Turner:

I'm reading this sentence over and over, but it still doesn't make
sense to me.

But, but....

You have events and members. You also have xm2e, which defines what
members are 'attached' to what events.
Now, there are several possibilities:

1. A member is not attached to an event. So, there is no xm2e-record,
which contains both the memberID and the EventID. These are the ones
you want for a given, right?

2. A xm2e-record for this combination of memberID and EventID exists.
In that case, the "myReply"-field either contains "Yes, I will", or
"No, I can't". For this list, you're not interested in these
occurrences. Right?



SELECT event.eventID,
UNIX_TIMESTAMP(eventName), // :-(
typeName
FROM event
INNER JOIN type ON
type.typeID = event.typeID
LEFT JOIN xm2e ON
xm2e.eventID = event.eventID AND
(xm2e.memberID = $member) // don't care what he replied
WHERE event.startDateTime > NOW()
GROUP BY event.eventID
HAVING COUNT(xID)=0


--
Erick

On Fri, 12 Mar 2010 10:37:20 +0000, Erick T. Barkhuis wrote:

Absolutely but then my suite of scripts would bork. It relies on every
table having a unique primary index xxxxID and the second column having
xxxxName where xxxx is the same. I don't give the events names at all,
the 'name' is a generated from the date/time, location and type of event.

On Fri, 12 Mar 2010 11:11:02 +0000, Erick T. Barkhuis wrote:

Right.
Wrong. There is no myReply field. The idea is to put the eventID in the
noCanDo column instead. That way the members are attached to the events
in one of two ways: either as yes (the old eventID column from the time
when it was a simple many-to-many) or as no (the new noCanDo column). The
idea is to be able to nag those members who have not yet replied and
enable them to sign up (or refuse) online. Also, once a week I want to
produce a spreadsheet which lists members down the side, events along the
top and yes, no or blank in the cells. xm2e is really two cross-reference
tables in one: not elegant but it does keep the data all in one place.

Derek Turner:

Sorry Derek, but that's plain wrong (IMO). Your suite was designed to
have an eventID to identify the record, and an eventName to name that
particular instance. The name need not be an identifier and is not
necessarily unique.
The suite wouldn't bork when you name your event properly.

Also, the Location name is not an attribute of Event. And certainly not
part of an identifier. You would want a foreign key LocID, which points
to the LocID of the Locations table. There, you store attributes of
Location (name, max seats, address, etc.)

Type of Event is also a foreign key to the EventType table (and so,
it's not part of the Event name). The EventType table would have a
TypeID, and then attributes like TypeDescription, SecurityAvailableYN,
etc.

It is my impression, that you need a sound Data Model first, before
trying to squeeze any functionality in. Your current table
implementation will sooner or (not likely) later give you many
problems, when you try to extend to application with more functions.

--
Erick