Search Marketing

   
   

Comparing Data in different Tables Using IN, NOT IN

Tue Aug 17, 2010 1:59 am
<<     >>
Comments: 3 Views: 1945

Using IN and NOT IN, in a variety of everyday database issues.

If we have the following database tables:

mysql> select * from Name;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+
mysql> select * from Work;
+----+---------------+--------+
| ID | Location | NameID |
+----+---------------+--------+
| 1 | New York | 3 |
| 2 | San Francisco | 2 |
+----+---------------+--------+

And if we want only those names where we do not have a work location for the entry:

mysql> select ID,Name from Name where ID NOT IN (select NameID from Work);
+----+--------+
| ID | Name |
+----+--------+
| 1 | Joe |
| 4 | James |
| 5 | George |
+----+--------+


And we want to be able to select only those names who we have a work location for, then the following statement will be:

mysql> select ID,Name from Name where ID IN (select NameID from Work);
+----+------+
| ID | Name |
+----+------+
| 2 | Jack |
| 3 | John |
+----+------+


As in most situations relating to programming, or mathematics, there are many ways in which to solve a problem. The following statement will also work to select which Names have work locations.

mysql> select n.ID,n.Name from Name n, Work w where n.ID=w.NameID;
+----+------+
| ID | Name |
+----+------+
| 3 | John |
| 2 | Jack |
+----+------+



Comments
Thu Apr 09, 2009 2:58 am
Name: ,n | Comment: mn

Wed May 20, 2009 11:19 pm
Name: David Castillo | Comment: A++
Thanks for your help!
Regards,
David

Tue Aug 17, 2010 1:59 am
Name: Zachary | Comment: Thanks a lot! this helped me loads !

Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (27 + 5)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc