Right list.
What you want is an outer join. Very simple example:
person
---
personID
===
name
region
===
attendance
---
attendanceID
===
date
personID FK into person
eventid FK into event
===
Select event.date,
person.name,
(attendance.personID is null) as present
from attendance left outer join person on attendance.personID =
person.personID
order by person.region
SHOULD give you a 0 in present if they were not there.
(DISCLAIMER: It's late, I've not tested this code, and this is free advice,
take it for what it's worth.) :)
HTH,
=C+
*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*
-----Original Message-----
From: Chris Griffin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 02, 2002 9:08 PM
To: [EMAIL PROTECTED]
Subject: SQL design/query question
If this is not the right list for this please direct me to the proper
list.
I have a table of name/address information. I need to keep track of a
weekly event and if they participated or not. I thought I could have a
table of dates and people record ids with just those that participated.
The problem is I want to list all people with a check or not if they
participated. I would like the list sorted by region (which is part of
the people table) and by participation within that. Is this possible to
do with the way I have set up the tables? If so what is the query. I can
only seem to create a query that only includes the ones that
participate. If I can't do it this way is there another way that would
keep the concept of a 'sparse matrix' in the participated table? I would
like to do this so I don't have to have a record for every person for
each week. Thanks.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
