RE: solution for opposite of this join / join from this subselect

2003-01-23 Thread M Wells 
 exactly the same recordset as the RIGHT JOIN example above it.
So, the 'direction' of the join indicates which side of the tables
listed in the FROM clause will return all of their rows, and which side
will only return the data matched by the ON clause (and which can then
usually be further qualified / restrained by WHERE clauses).

In theory, it should be possible to retrieve a recordset that combines
the LEFT JOIN and RIGHT JOIN results from our examples above in a single
recordset. 

This would probably look something like:

NULL,NULL,project2
employee1,employee details1,project1
employee2,employee details2,project3
employee3,employee details3,NULL
employee4,employee details4,project4

This is usually performed by a FULL JOIN, but I am under the impression
that MySQL has yet to implement FULL JOINs (certainly, I can't get them
to work on ver 3.23.53 and the documentation for my version indicates
FULL JOINs are expected in ver 4.0 or 4.1).

Now, to go back to your original question, the people / events /
attendance example employed both a LEFT JOIN and a RIGHT JOIN because
the attendance table was acting as an intermediary between the people
and events tables. From my Microsoft Acess background, I would call this
a 'FIND UNMATCHED' query, although instead of finding the records in one
table that aren't reflected in another [1], we're finding and combining
the records from two tables that aren't associated with each other in a
third. I personally can't think of a way of representing the same query
you have developed but only using LEFT JOINs. I'm not in a position to
say it can't be done, but I certainly can't think of a way to do it.

All the best,

M Wells

[1] As a simple example of a 'find unmatched' query, if we had a table
that contained a record for each of the files on your computer and a
table of all of the files from your computer that you've backed up onto
a CD, then finding the records in your [allfiles] table that are
unmatched (i.e. don't exist) in your [backeduptocd] table should equate
to a list of all of the files on your computer that have yet to be
backed up to CD

-Original Message-
From: Josh L Bernardini [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, 23 January 2003 5:24 AM
To: [EMAIL PROTECTED]
Subject: solution for opposite of this join / join from this subselect

thanks to brent, bob and M wells for their contributions to this
solution
and to m especially who seems to have put in a lot of time and nailed
it.

This query returns a list of people not attending a particular event,
given
an events table, a people table, and a many-many epeople table between
them. You can modify the where clause to show all the people attending a
particular event, all the events a person isn't/is attending - most of
what
you might need in most many - many relationships.

But I still haven't figured out the importance of left joins vs. right
joins. can anyone explain why this statement requires a right join to
work?
could it be rewritten to use a left join?

mysql SELECT lastname, firstname, title, event
- FROM people p
- LEFT JOIN epeople ep on p.id = ep.pid
- right join events e ON e.id = ep.eid
- WHERE ep.pid IS NULL
- and ep.eid is null
- and e.id=2
- ORDER BY e.id;


I reworte the statement like this and from doing so understand that, OK
epeople as the pivot point between the two tables needs to be on the
outside of the join  - really placing it between the two tables. we're
looking for people where there is no record match for epeople.
SELECT lastname, firstname, title, event
FROM events e
LEFT JOIN epeople ep on e.id = ep.eid
right join people p ON p.id = ep.pid
WHERE ep.pid IS NULL
and e.id=2
ORDER BY e.id;


now whats the functional difference between this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and e.id = 2
ORDER BY ep.eid;

and this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and ep.eid=2
ORDER BY e.id;

as written the difference is in the and statements but in my result set
ep.eid == e.id == 2 so why can't you use the second statement
interchangably with the first?

thanks for any insights,
jb



-
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

Re: solution for opposite of this join / join from this subselect

2003-01-23 Thread Bill Easton 
See interleaved comments below.

 Subject: solution for opposite of this join / join from this subselect
 To: [EMAIL PROTECTED]
 From: Josh L Bernardini [EMAIL PROTECTED]
 Date: Wed, 22 Jan 2003 11:23:44 -0800

 thanks to brent, bob and M wells for their contributions to this solution
 and to m especially who seems to have put in a lot of time and nailed it.

 This query returns a list of people not attending a particular event,
given
 an events table, a people table, and a many-many epeople table between
 them. You can modify the where clause to show all the people attending a
 particular event, all the events a person isn't/is attending - most of
what
 you might need in most many - many relationships.

 But I still haven't figured out the importance of left joins vs. right
 joins. can anyone explain why this statement requires a right join to
work?
 could it be rewritten to use a left join?

 mysql SELECT lastname, firstname, title, event
 - FROM people p
 - LEFT JOIN epeople ep on p.id = ep.pid
 - right join events e ON e.id = ep.eid
 - WHERE ep.pid IS NULL
 - and ep.eid is null
 - and e.id=2
 - ORDER BY e.id;

It can.  How about:

mysql SELECT lastname, firstname, title, event
- FROM events e
-   LEFT JOIN (people p
-  LEFT JOIN epeople ep on p.id = ep.pid)
-   ON e.id = ep.eid
- WHERE ep.pid IS NULL
- and ep.eid is null
- and e.id=2
- ORDER BY e.id;

This appears to work in MySQL 4.0.9; Mysql 3.23 didn't allow the
parentheses.
In fact, 4.0.9 appears to work without the parentheses, but 3.23 still
doesn't.
The point is, you want all of the people and all of the events, so you have
to have people on the left side of a left join and events on the left side
of a left join.

[...]

 now whats the functional difference between this

 SELECT lastname, firstname, title, event
 FROM people p
 LEFT JOIN epeople ep on p.id = ep.pid
 right join events e on ep.eid = e.id
 WHERE ep.pid IS NULL
 and e.id = 2
 ORDER BY ep.eid;

 and this

 SELECT lastname, firstname, title, event
 FROM people p
 LEFT JOIN epeople ep on p.id = ep.pid
 right join events e on ep.eid = e.id
 WHERE ep.pid IS NULL
 and ep.eid=2
 ORDER BY e.id;

 as written the difference is in the and statements but in my result set
 ep.eid == e.id == 2 so why can't you use the second statement
 interchangably with the first?

No, it's not so that ep.eid == e.id.
In the result of the join, before applying the where clause, ep.eid is null
and e.id is not null for a row corresponding to an event which has no
associated people.

 thanks for any insights,
 jb






-
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

solution for opposite of this join / join from this subselect

2003-01-22 Thread Josh L Bernardini 
thanks to brent, bob and M wells for their contributions to this solution
and to m especially who seems to have put in a lot of time and nailed it.

This query returns a list of people not attending a particular event, given
an events table, a people table, and a many-many epeople table between
them. You can modify the where clause to show all the people attending a
particular event, all the events a person isn't/is attending - most of what
you might need in most many - many relationships.

But I still haven't figured out the importance of left joins vs. right
joins. can anyone explain why this statement requires a right join to work?
could it be rewritten to use a left join?

mysql SELECT lastname, firstname, title, event
- FROM people p
- LEFT JOIN epeople ep on p.id = ep.pid
- right join events e ON e.id = ep.eid
- WHERE ep.pid IS NULL
- and ep.eid is null
- and e.id=2
- ORDER BY e.id;


I reworte the statement like this and from doing so understand that, OK
epeople as the pivot point between the two tables needs to be on the
outside of the join  - really placing it between the two tables. we're
looking for people where there is no record match for epeople.
SELECT lastname, firstname, title, event
FROM events e
LEFT JOIN epeople ep on e.id = ep.eid
right join people p ON p.id = ep.pid
WHERE ep.pid IS NULL
and e.id=2
ORDER BY e.id;


now whats the functional difference between this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and e.id = 2
ORDER BY ep.eid;

and this

SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and ep.eid=2
ORDER BY e.id;

as written the difference is in the and statements but in my result set
ep.eid == e.id == 2 so why can't you use the second statement
interchangably with the first?

thanks for any insights,
jb



-
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

opposite of this join

2003-01-21 Thread Josh L Bernardini 

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.


tia,
jb




-
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

opposite of this join? -- simplified

2003-01-21 Thread Josh L Bernardini 
I would like to get the difference of the results from query 2 and query 1,
or the people not attending event [id 2] (referenced by epeople.eid).
Thought the answer would be query three, but as you see, I get an empty
set. Any advice would be appreciated.
jb


Here is everyone attending the event [id 2]


1) mysql select people.id as pid, concat(lastname, , , firstname) as
name from
- epeople
-   left join people on epeople.pid=people.id
-  where epeople.eid=2;
+--+--+
| pid  | name |
+--+--+
|5 | Piovesan, Ron|
|6 | Mossberg, Walt   |
|7 | Reporter, Jon|
|8 | Analyst, Jon |
|   12 | Media, Karen |
|   15 | Galvin, Tom  |
|   16 | Ogilvy, Tom  |
|   17 | Machines, Industrial |
|2 | Tenderich, Burghardt |
+--+--+
9 rows in set (0.01 sec)




Here are all the people records:

2) mysql select people.id as pid, concat(lastname, , , firstname) as
name from
- people;
+-+--+
| pid | name |
+-+--+
|   1 | Collins, Stacey  |
|   2 | Tenderich, Burghardt |
|   3 | Executive, Bea   |
|   4 | Bea, Joe |
|   5 | Piovesan, Ron|
|   6 | Mossberg, Walt   |
|   7 | Reporter, Jon|
|   8 | Analyst, Jon |
|   9 | Smith, Susan |
|  10 | Jane, Sarah  |
|  12 | Media, Karen |
|  13 | PR, Bea  |
|  14 | Relations, Public|
|  15 | Galvin, Tom  |
|  16 | Ogilvy, Tom  |
|  17 | Machines, Industrial |
+-+--+
16 rows in set (0.00 sec)


3) mysql select people.id as pid, concat(lastname, , , firstname) as
name from
- epeople
-   left join people on epeople.pid=people.id
-  where epeople.eid=2
-  and people.id is null;







-
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

Re: opposite of this join

2003-01-21 Thread Bob Hall 
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

Re: opposite of this join

2003-01-21 Thread Brent Baisley 
You are looking for a list of people rather than a list of events and 
the people attending them. So you want to start your select from People, 
not from Events. Get a list of all the people, then filter out those 
attending event id 2. You don't want to start you select from epeople 
because then you are excluding those people that are not attending any 
event.

select people.id as pid,concat(lastname,, ,firstname) as name
from people
left join epeople on epeople.pid=people.id
left join events on events.id=epeople.eid
where events.id!=2

I think will do it, although something feels wrong. Hmmm, let me know.


On Tuesday, January 21, 2003, at 11:43 AM, Josh L Bernardini wrote:

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;


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


-
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

Re: opposite of this join

2003-01-21 Thread Josh L Bernardini 

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

Re: opposite of this join

2003-01-21 Thread Bob Hall 
On Tue, Jan 21, 2003 at 01:38:42PM -0800, Josh L Bernardini wrote:
 
 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;

It's been a while since I worked with this query, so I'm not sure that 
this answer is correct. However, after quickly going over the article 
again, I believe you can treat the attends column as a boolean type. 
Add AND attends = FALSE to the WHERE clause to get the events they 
will not attend. If that works, it answers both of your questions.

(You can also use attends = 0, but attends = FALSE is more 
self-documenting.)


HTH
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

Re: opposite of this join

2003-01-21 Thread Josh L Bernardini 

Thats what was strange with the results. One of the meetings the user was
attending had a value of 9, the other 0.
Might just give up doing this and use 2 queries to accomplish the same so
don't spend anymore time on it unless your curious yourself.
thanks just the same,
jb


|-+
| |   Bob Hall   |
| |   rjhalljr@starpow|
| |   er.net  |
| ||
| |   01/21/2003 06:57 |
| |   PM   |
| ||
|-+
  
--|
  |
  |
  |   To:   [EMAIL PROTECTED]  
  |
  |   cc:  
  |
  |   Subject:  Re: opposite of this join  
  |
  
--|




On Tue, Jan 21, 2003 at 01:38:42PM -0800, Josh L Bernardini wrote:

 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;

It's been a while since I worked with this query, so I'm not sure that
this answer is correct. However, after quickly going over the article
again, I believe you can treat the attends column as a boolean type.
Add AND attends = FALSE to the WHERE clause to get the events they
will not attend. If that works, it answers both of your questions.

(You can also use attends = 0, but attends = FALSE is more
self-documenting.)


HTH
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

RE: opposite of this join

2003-01-21 Thread M Wells 
 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

Re: opposite of this join

2003-01-21 Thread Bob Hall 
In Sum(), join people IDs to people IDs, not to event IDs.

In the article, the SQL statement is mostly ok, but the explanation 
section proves, once again, that I shouldn't be proofreading my own stuff. 
I've cleaned it up, so there won't be any more errors until I look at  
it the next time. 

On Tue, Jan 21, 2003 at 07:34:47PM -0800, Josh L Bernardini wrote:
 
 Thats what was strange with the results. One of the meetings the user was
 attending had a value of 9, the other 0.
 Might just give up doing this and use 2 queries to accomplish the same so
 don't spend anymore time on it unless your curious yourself.
 thanks just the same,
 jb
 
 
 |-+
 | |   Bob Hall   |
 | |   rjhalljr@starpow|
 | |   er.net  |
 | ||
 | |   01/21/2003 06:57 |
 | |   PM   |
 | ||
 |-+
   
--|
   |  
|
   |   To:   [EMAIL PROTECTED]
|
   |   cc:
|
   |   Subject:  Re: opposite of this join
|
   
--|
 
 
 
 
 On Tue, Jan 21, 2003 at 01:38:42PM -0800, Josh L Bernardini wrote:
 
  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;
 
 It's been a while since I worked with this query, so I'm not sure that
 this answer is correct. However, after quickly going over the article
 again, I believe you can treat the attends column as a boolean type.
 Add AND attends = FALSE to the WHERE clause to get the events they
 will not attend. If that works, it answers both of your questions.
 
 (You can also use attends = 0, but attends = FALSE is more
 self-documenting.)



-
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