On 09-Jul-01 Asha Ramaiah wrote:
> Hey there!..Don!
> The Problemo returns! :(
> Let me be explicit this time with my query :
>
> mysql> select * from table A;
> +-------------+--------+
>| mId | tId |
> +-------------+--------+
>| 1 | 1 |
>| 2 | 1 |
>| 2 | 6 |
> +-------------+--------+
> 3 rows in set (0.00 sec)
>
mysql> select * from a;
+---+---+
| m | t |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 2 | 6 |
+---+---+
3 rows in set (0.00 sec)
mysql> select * from b;
+---+-------+
| t | name |
+---+-------+
| 1 | one |
| 2 | two |
| 3 | foo |
| 4 | bar |
| 5 | baz |
| 6 | phsst |
| 7 | bogon |
+---+-------+
7 rows in set (0.00 sec)
You want every thing in table b that is not tId=1, right ?
So find the combination product of table a that's invalid:
mysql> select a.*,x.*
-> from a left join a as x using(t) where a.m=1;
+---+---+------+------+
| m | t | m | t |
+---+---+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 1 |
+---+---+------+------+
2 rows in set (0.00 sec)
Query table b for rows not in the product:
mysql> select distinct b.*
-> from a left join a as x using(t), b
-> where a.m=1 and b.t not in (a.t, x.t);
+---+-------+
| t | name |
+---+-------+
| 2 | two |
| 3 | foo |
| 4 | bar |
| 5 | baz |
| 6 | phsst |
| 7 | bogon |
+---+-------+
6 rows in set (0.00 sec)
Okey-dokey, that'll work for your example case; the problem is as you
get additional 't' values ...
mysql> insert into a values (1,4);
mysql> select * from a;
+---+---+
| m | t |
+---+---+
| 1 | 1 |
| 1 | 4 |
| 2 | 1 |
| 2 | 6 |
+---+---+
4 rows in set (0.00 sec)
b.tId 1 & 4 are invalid; you'll need another self left join for each t value;
plus another term in the ' b.t not in (a.t, x.t, y.t ...)'
So for the robust case, make a temp table of 'bad' values:
create table bad(t int not null unique);
mysql> replace into bad
-> select t from a where m=1;
And fetch the b rows that aren't in bad ...
mysql> select b.*
-> from b left join bad using(t)
-> where bad.t is null;
+---+-------+
| t | name |
+---+-------+
| 2 | two |
| 3 | foo |
| 5 | baz |
| 6 | phsst |
| 7 | bogon |
+---+-------+
5 rows in set (0.01 sec)
drop table bad;
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
---------------------------------------------------------------------
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
