Hi Josh,
I'm hesitant to make the following suggestion, mainly because I'm not
terribly clued up with SQL myself, however the following APPEARS to
deliver something close to what you're looking for. It relies on
breaking your recordsets into 3 tables, as your situation appears to
already do, 1 for People (tblPeople), 1 for Events (tblEvents), 1
indicating which People are attending which Events (tblAttendance).
The tblPeople table contains just one record per person in the database.
Similarly, the tblEvents table contains just one record per event.
tblAttendance, however, contains a record for each Event a Person is
attending. From what I read of your original post, this appears to be
the approach you are already taking.
The task, as I understand it, is to return a recordset indicating which
Events are NOT being attended by which People?
To demonstrate the SQL I used, I created the following example tables
and inserted the following example records:
CREATE TABLE `tblPeople` (`personid` int NOT NULL AUTO_INCREMENT ,
`surname` varchar(100) NOT NULL DEFAULT '' , `givennames` varchar(100)
NOT NULL DEFAULT '' , `contactdetails` varchar(100) NOT NULL DEFAULT ''
, PRIMARY KEY (`personid`)) TYPE=MYISAM ROW_FORMAT=DEFAULT
CREATE TABLE `tblEvents` (`eventid` int NOT NULL AUTO_INCREMENT ,
`eventname` varchar(100) NOT NULL DEFAULT '' , `eventdesc` varchar(100)
NOT NULL DEFAULT '' , PRIMARY KEY (`eventid`)) TYPE=MYISAM
ROW_FORMAT=DEFAULT
CREATE TABLE `tblAttendance` (`attendid` int NOT NULL AUTO_INCREMENT ,
`personid` int NOT NULL, `eventid` int NOT NULL, PRIMARY KEY
(`attendid`)) TYPE=MYISAM ROW_FORMAT=DEFAULT;
INSERT INTO tblPeople (surname, givennames, contactdetails) VALUES
('surname 1', 'given names 1', 'contact details 1');
INSERT INTO tblPeople (surname, givennames, contactdetails) VALUES
('surname 2', 'given names 2', 'contact details 2');
INSERT INTO tblPeople (surname, givennames, contactdetails) VALUES
('surname 3', 'given names 3', 'contact details 3');
INSERT INTO tblPeople (surname, givennames, contactdetails) VALUES
('surname 4', 'given names 4', 'contact details 4');
INSERT INTO tblEvents (eventname, eventdesc) VALUES ('event 1', 'event
description 1');
INSERT INTO tblEvents (eventname, eventdesc) VALUES ('event 2', 'event
description 2');
INSERT INTO tblEvents (eventname, eventdesc) VALUES ('event 3', 'event
description 3');
INSERT INTO tblEvents (eventname, eventdesc) VALUES ('event 4', 'event
description 4');
INSERT INTO tblAttendance (personid, eventid) VALUES (1,1);
INSERT INTO tblAttendance (personid, eventid) VALUES (1,2);
INSERT INTO tblAttendance (personid, eventid) VALUES (1,3);
INSERT INTO tblAttendance (personid, eventid) VALUES (2,2);
INSERT INTO tblAttendance (personid, eventid) VALUES (2,3);
INSERT INTO tblAttendance (personid, eventid) VALUES (2,4);
INSERT INTO tblAttendance (personid, eventid) VALUES (3,1);
INSERT INTO tblAttendance (personid, eventid) VALUES (3,3);
INSERT INTO tblAttendance (personid, eventid) VALUES (3,4);
INSERT INTO tblAttendance (personid, eventid) VALUES (4,1);
In the tables above we see that Person1 attended Event1, Event2 and
Event3 but didn't attend Event4. Person2 attended Event2, Event3 and
Event4 but didn't attend Event1. Person3 attended Event1, Event3 and
Event4 but didn't attend Event2. And Person4 only attended Event1,
missing out on attending Event2, Event3 and Event4.
If I understand your needs correctly, this means you need to return a
recordset indicating Person1 didn't attend Event4, Person2 didn't attend
Event1, Person 3 didn't attend Event2 and Person4 didn't attend Event2,
Event3 or Event4?
Okay, first things first -- the following SQL statement returns a
recordset indicating which events which people DID attend:
SELECT tp.surname, tp.givennames, tp.contactdetails, te.eventname,
te.eventdesc FROM tblPeople tp, tblEvents te, tblAttendance ta WHERE
tp.personid = ta.personid AND te.eventid = ta.eventid;
The recordset returned by this statement looks something like:
'surname 1','given names 1','contact details 1','event 1','event
description 1'
'surname 1','given names 1','contact details 1','event 2','event
description 2'
'surname 1','given names 1','contact details 1','event 3','event
description 3'
'surname 2','given names 2','contact details 2','event 2','event
description 2'
'surname 2','given names 2','contact details 2','event 3','event
description 3'
'surname 2','given names 2','contact details 2','event 4','event
description 4'
'surname 3','given names 3','contact details 3','event 1','event
description 1'
'surname 3','given names 3','contact details 3','event 3','event
description 3'
'surname 3','given names 3','contact details 3','event 4','event
description 4'
'surname 4','given names 4','contact details 4','event 1','event
description 1'
Now, the following SQL statement identifies which events which people
DIDN'T attend:
SELECT tp.surname, tp.givennames, tp.contactdetails, te.eventname,
te.eventdesc FROM tblPeople tp LEFT JOIN tblAttendance ta on tp.personid
= ta.personid RIGHT JOIN tblEvents te ON te.eventid = ta.eventid WHERE
ta.personid IS NULL AND ta.eventid IS NULL ORDER BY tp.surname,
tp.givennames, te.eventname;
This returns a recordset that looks something like (which also matches
the exepectation expressed above):
'surname 1','given names 1','contact details 1','event 4','event
description 4'
'surname 2','given names 2','contact details 2','event 1','event
description 1'
'surname 3','given names 3','contact details 3','event 2','event
description 2'
'surname 4','given names 4','contact details 4','event 2','event
description 2'
'surname 4','given names 4','contact details 4','event 3','event
description 3'
'surname 4','given names 4','contact details 4','event 4','event
description 4'
To specifically return the Events a PARTICULAR Person didn't attend,
simply ammend the WHERE clause in the following way:
SELECT tp.surname, tp.givennames, tp.contactdetails, te.eventname,
te.eventdesc FROM tblPeople tp LEFT JOIN tblAttendance ta on tp.personid
= ta.personid RIGHT JOIN tblEvents te ON te.eventid = ta.eventid WHERE
ta.personid IS NULL AND ta.eventid IS NULL AND tp.personid = 4 ORDER BY
te.eventname;
This returns a recordset just for Person4, looking like:
'surname 4','given names 4','contact details 4','event 2','event
description 2'
'surname 4','given names 4','contact details 4','event 3','event
description 3'
'surname 4','given names 4','contact details 4','event 4','event
description 4'
I hope this is of some help and doesn't just confuse matters more.
Regards and best wishes,
Murray Wells,
Urban Legend Web Design (ABN 18 635 979 727)
0402 456 813
-----Original Message-----
From: Josh L Bernardini [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 22 January 2003 7:39 AM
To: Bob Hall
Cc: [EMAIL PROTECTED]
Subject: Re: opposite of this join
Bob,
First of all, thank you. I never would have gotten here on my own. Only
I
am not quite there.
Using your example, I have managed to list all the events a person is
attending and not attending. Wondering if you might provide some further
clues as to how to restrict the query to those events a person is not
attending, or in you example, those items a person doesn't own.
This step is towards the goal of returning all the people not attending
an
event - or not owning a desk.
Also what is the significance of the value returned in the attends
column?
Here's what I've got:
mysql> SELECT events.event,
-> people.lastname,
-> Sum(epeople.eid = people.id) AS attends
-> FROM (events, people) LEFT JOIN epeople
-> ON events.id = epeople.eid
-> WHERE people.id=1
-> Group by events.id;
+--------------------------+----------+---------+
| event | lastname | attends |
+--------------------------+----------+---------+
| BEA World, San Francisco | Collins | 9 |
| The Storage Consortium | Collins | 0 |
| BEA II | Collins | 0 |
+--------------------------+----------+---------+
3 rows in set (0.01 sec)
>From your many-many example, here are those events this individual is
attending:
mysql> SELECT event, lastname
-> FROM people, epeople, events
-> WHERE people.id = epeople.pid
-> AND epeople.eid = events.id
-> AND people.id=1;
+--------------------------+----------+
| event | lastname |
+--------------------------+----------+
| BEA World, San Francisco | Collins |
| BEA II | Collins |
+--------------------------+----------+
2 rows in set (0.00 sec)
thanks again,
jb
|---------+---------------------------->
| | "Bob Hall" |
| | <rjhalljr@starpow|
| | er.net> |
| | |
| | 01/21/2003 10:49 |
| | AM |
| | |
|---------+---------------------------->
>-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Subject: Re: opposite of this join
|
>-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
On Tue, Jan 21, 2003 at 08:43:29AM -0800, Josh L Bernardini wrote:
>
> I have three tables, people, events and epeople. epeople includes a
person
> id and an event id and works as a list of people attending events in
the
> database.
>
> The following query returns a list of all the people participating in
a
> particular event
> given an event id.
>
> select people.id as pid, concat(lastname, ", ", firstname) as name
from
> events
> left join epeople on events.id=epeople.eid
> left join people on epeople.pid=people.id
> where events.id=2;
>
> How could I get a list of all the people not participating?
> thought I would add to the where clause with:
> and people.id is null;
>
> but that returns an empty set.
Right. Presumably, there's no entry in your join table to link a
person to an event that they're not attending.
I've got a entry in the MySQL SQL section of my website called
"Whether a row on one side of a many-to-many join is linked to
a given row on the other side" that has a solution.
http://users.starpower.net/rjhalljr/Serve
Bob Hall
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
