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 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 <mysql-unsubscribe- > [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
