Hi Tim,
> Assume I have two tables, A and B, and both share a key field (for the
> sake of argument, lets call it key_id). Is there a way to determine for
> which values of key_id in A there is no corresponding row in B?
> e.g I might have Table A:
> key_id , value
> 1,1
> 2,1
> 3,3
> 4,4
>
> and Table B
> key_id, othervalue
> 1,4
> 3,4
>
> I basically want to know if there's a query that return's 2 and 4 as
> key_id's in A but missing from B?
> Any sql gurus out there?
I have a sense of deja-vu about this question. How many times have you asked?
> Everyone talks about apathy, but no one does anything about it.
So where is your current-best SQL query? (or does your reference to apathy mean that
you don't actually write
code yourself?)
Start at 6.4.1 SELECT Syntax and you will soon jump to 6.4.1.1 JOIN Syntax.
There is an example/answer for you in the text!
If you want to get ahead of the game and haven't met them before, check out the
tutorial 3.3.4.6 Working with
NULL Values.
Now (seeing you have invited sarcasm), ask about gurus and apathy again!?
Ok, let's get that tongue in cheek, out and bite your lip in preparation for some
concentration...
Start with a first attempt:
mysql> SELECT *
-> FROM a NATURAL JOIN b;
+--------+-------+--------+------------+
| Key_ID | Value | Key_ID | OtherValue |
+--------+-------+--------+------------+
| 3 | 3 | 3 | 4 |
| 1 | 1 | 1 | 4 |
+--------+-------+--------+------------+
2 rows in set (0.00 sec)
Doesn't work - we knew that much already duh!
- let's force every row in tbl a to appear in the resultset:
mysql> SELECT *
-> FROM a LEFT JOIN b USING (Key_ID);
+--------+-------+--------+------------+
| Key_ID | Value | Key_ID | OtherValue |
+--------+-------+--------+------------+
| 1 | 1 | 1 | 4 |
| 2 | 1 | NULL | NULL |
| 3 | 3 | 3 | 4 |
| 4 | 4 | NULL | NULL |
+--------+-------+--------+------------+
4 rows in set (0.00 sec)
Ok, now we can see what we want/scent victory
- only want the rows=NULL response from tbl b:
mysql> SELECT *
-> FROM a LEFT JOIN b USING (Key_ID)
-> WHERE OtherValue IS NULL;
+--------+-------+--------+------------+
| Key_ID | Value | Key_ID | OtherValue |
+--------+-------+--------+------------+
| 2 | 1 | NULL | NULL |
| 4 | 4 | NULL | NULL |
+--------+-------+--------+------------+
2 rows in set (0.00 sec)
Don't want all that extraneous/debugging cluttering up the resultset:
mysql> SELECT a.KEY_ID
-> FROM a LEFT JOIN b USING (Key_ID)
-> WHERE OtherValue IS NULL;
+--------+
| KEY_ID |
+--------+
| 2 |
| 4 |
+--------+
2 rows in set (0.00 sec)
Because both table use same field name, can get rid of the USING clause and really
strut our stuff:
mysql> SELECT a.KEY_ID
-> FROM a NATURAL LEFT JOIN b
-> WHERE OtherValue IS NULL;
+--------+
| KEY_ID |
+--------+
| 2 |
| 4 |
+--------+
2 rows in set (0.00 sec)
Et voila!
=dn
(practicing to become a guru - next stage of enlightenment, the bed of neils - woah
there, do you spell it with
an "a"?)
---------------------------------------------------------------------
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
