Well maybe I sould map this out more clearly...
members
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| rowid | int(11) | | PRI | NULL| auto_increment |
| first_name| varchar(20) | YES | | NULL||
| last_name | varchar(20) | YES | | NULL||
| address1 | varchar(60) | YES | | NULL||
| address2 | varchar(60) | YES | | NULL||
| city | varchar(20) | YES | | NULL||
| state | varchar(20) | YES | | NULL||
| zip | varchar(12) | YES | | NULL||
| region_id | int(10) | YES | | NULL||
| subregion_id | int(11) | YES | | NULL||
+---+-+--+-+-++
regions
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| rowid | bigint(20) | | PRI | NULL| auto_increment |
| name | varchar(20) | YES | | NULL||
+---+-+--+-+-++
subregions
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| rowid | bigint(20) | | PRI | NULL| auto_increment |
| name | varchar(20) | YES | | NULL||
+---+-+--+-+-++
event
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-++
| rowid| int(11) | | PRI | NULL| auto_increment |
| event_type | smallint(6) | YES | | NULL||
| event_date | date| YES | | NULL||
| member_id| int(11) | YES | | NULL||
+--+-+--+-+-++
the results of the query should look like this:
+--+-+++
--+
| region | subregion | first_name |
last_name | Attended |
+--+-+++
--+
I am interested in showing a list for a selected date so the date is not
part of the output. I am currently using the following query to just
show the list without the attendance information.
SELECT regions.name as region, subregion.name as subregion, first_name,
last_name FROM members, regions, subregion WHERE
members.region_id=regions.rowid and members.subregion_id=subregion.rowid
GROUP BY region, subregion;
+--+-+++
| region | subregion | first_name |
last_name |
+--+-+++
The query you gave didn't seem to work right. It only gave me one line
for every record in the event table.
On Tuesday, July 2, 2002, at 08:45 PM, Cal Evans wrote:
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
