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
Re: opposite of this join
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
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
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
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
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
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
