Hello sql guru's, I need help find SQL to solve a problem:
I have two tables, the first is persons:
person_id int unsigned not null auto_increment
firstname varchar(25)
lastname varchar(25)
The second is person_orgs
person_id int unsigned not null
org_id int unsigned not null
A person may be assigned more than one org by using person_orgs table, but
they can only be assigned to an org one time by way of application
rules/logic. Here is my problem:
I need to select a list of persons that have not ever been assigned to an
org OR have not been assigned to a specific org, such as org_id = 1, so I
tried:
select p.firstname,p.lastname from persons p left join person_orgs po on
p.person_id = po.person_id where (po.org_id <> 1 OR po.og_id IS NULL)
I thought this was correct, and it does return persons never assigned
before, but all it does concerning the specific org_id is to leave out the
one record where the person is assigned to org_id = 1. If the person is
assigned to org_id =1 plus any additional, then the record for the other
org(s) is/are returned, giving me a person I don't need.
I have no idea how to do this. It is almost as if I need to transform:
person_id org_id
1 1
1 2
1 3
into:
person_id org1 org2 org3
1 1 2 3
but I don't really know how in sql, and the number of orgs will be different
for each person. I know I could build some large associative array in my
language of choice and sort through it somehow, that just seems slow and
cumbersome.
I feel there is some SQL based solution, I just can't grasp it.
Can anyone else?
Thanks,
Brian
---------------------------------------------------------------------
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
