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
