Re: Query Help

[Chris]

On Sun, 27 Jun 1999 PinkeshP@stripped wrote:
   
> I have two table 1) register 2) portfolio both are related by column name id.
> lets say for example register table has following values in id column
>  
> 1
> 2
> 3
> 
> and  portfolio table has following values in id column
>>
> 1
> 2
> 3
> 4
> 5
>  
> how can i write a query so it will display the ids that are not present 
> in register table (i.e. 4,5)
> 
> Pinkesh
>  
> ---------------------------------------------------------------------


Pinkesh, You can use 'LEFT JOIN' to accomplish this. Here is an example 
using the two tables you have described:

mysql> describe portfolio;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> describe register;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from register;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from portfolio;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> select portfolio.id as pid, register.id as rid from portfolio left
join register using (id);
+------+------+
| pid  | rid  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
|    5 | NULL |
+------+------+
5 rows in set (0.01 sec)

The left join will show 'null' in the register id column for id's that are
present in portfolio but not in register. Just look for the null columns
to get your result set:

mysql> select portfolio.id from portfolio left join register using (id)
where isnull(register.id);
+------+
| id   |
+------+
|    4 |
|    5 |
+------+
2 rows in set (0.00 sec)

--Chris