BS> Wow, never would have come up with that, thanks very much for the help.
I'm half way done with a programming version rather than sql. I will try
yours first.
BS> I guess this would work if person_id 3 was also assigned to org 1 as
well as org 2, they wouldn't show up the final results?
Yes. That's like person_id 4 in my example.
I don't know if you've got big tables, but I note that EXPLAIN tells me that
my query will generate a temporary table. Of course, in any event, you want
an index on person_orgs.person_id.
If you are going to have a temporary table, anyway, it might be clearer to
do it explicitly:
First, create a temporary table containing the person_id's you DON'T want --
i.e., those assigned to org_id 1:
mysql> create temporary table temp (person_id int not null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into temp
-> select p.person_id from persons p, person_orgs po
-> where p.person_id = po.person_id and po.org_id = 1;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+-----------+
| person_id |
+-----------+
| 2 |
| 4 |
+-----------+
2 rows in set (0.00 sec)
Then, use an outer join to get the ones you do want:
mysql> select p.person_id, p.name
-> from persons p left join temp t
-> on p.person_id = t.person_id
-> where t.person_id is null;
+-----------+------------+
| person_id | name |
+-----------+------------+
| 1 | unassigned |
| 3 | in org 2 |
+-----------+------------+
2 rows in set (0.00 sec)
You'll want an index on temp.person_id. You get to figure out which is
better for you.
Anyway, it was an interesting puzzle. I came up with the first--and most
offbeat--solution, because I was looking for a one-SQL-statement solution.
Hope this helps.
----- Original Message -----
From: Brian Smith
To: Bill Easton
Sent: Saturday, March 02, 2002 8:51 AM
Subject: RE: help me with complicate sql query
I guess this would work if person_id 3 was also assigned to org 1 as well
as org 2, they wouldn't show up the final results?
Brian/
-----Original Message-----
From: Bill Easton [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 01, 2002 7:46 PM
To: [EMAIL PROTECTED]
Cc: "Brian Smith"
Subject: Re: help me with complicate sql query
Well, it's possible, but it's not pretty.
The right way, of course, is to have subselects. Ah, well, someday...
You can't do it using just joins (inner or outer) and where clauses.
The reason is that the joins will give you a cross product and the on
clauses and the where clauses will throw away some of the rows. There
aren't any rows that indicate that a person is NOT a member of org_id = 1.
Here's a way of doing it using a GROUP BY clause. We need some way of
getting a row for each person and some way to identify whether that row has
a person we want. The trick--which works for a numeric column here--is to
group multiple rows by person and then look at the minimum absolute value of
(org_id - 1); this is zero precisely when the person is assigned to org_id -
1;
Here are the tables:
mysql> select * from persons;
+-----------+----------------+
| person_id | name |
+-----------+----------------+
| 1 | unassigned |
| 2 | in org 1 |
| 3 | in org 2 |
| 4 | in org 1 and 2 |
+-----------+----------------+
4 rows in set (0.00 sec)
mysql> select * from person_orgs;
+-----------+--------+
| person_id | org_id |
+-----------+--------+
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 4 | 2 |
+-----------+--------+
4 rows in set (0.00 sec)
Here's the first try, with just the GROUP BY. Note that the rows we
want have either null or a non-zero value in mn.
mysql> select p.person_id, p.name, min(abs(org_id - 1)) as mn
-> from persons p left join person_orgs po on p.person_id =
po.person_id
-> group by p.person_id;
+-----------+----------------+------+
| person_id | name | mn |
+-----------+----------------+------+
| 1 | unassigned | NULL |
| 2 | in org 1 | 0 |
| 3 | in org 2 | 1 |
| 4 | in org 1 and 2 | 0 |
+-----------+----------------+------+
4 rows in set (0.00 sec)
And here's the second try, where we use a HAVING clause to throw away
the rows we don't want:
mysql> select p.person_id, p.name, min(abs(org_id - 1)) as mn
-> from persons p left join person_orgs po on p.person_id =
po.person_id
-> group by p.person_id
-> having mn is null or mn <> 0;
+-----------+------------+------+
| person_id | name | mn |
+-----------+------------+------+
| 1 | unassigned | NULL |
| 3 | in org 2 | 1 |
+-----------+------------+------+
2 rows in set (0.00 sec)
> From: "Brian Smith" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Subject: help me with complicate sql query
> Date: Fri, 1 Mar 2002 10:48:20 -0500
> [...]
>
> 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
