getting unique records

I'm in need of a little sql help.

How do i get a list of ids from one table that do not have the same ids in
another table?

I hope that makes sense.

Re: getting unique records

Dave wrote:

> I'm in need of a little sql help.
>
> How do i get a list of ids from one table that do not have the same ids in
> another table?
>
> I hope that makes sense.
>
>

not exact syntax, but gives you an idea...


select a.id, b.id from table1 a left join table2 b on a.id=b.id where b.id is null;

mysql> create table t1 (c1 integer);
Query OK, 0 rows affected (2.27 sec)

mysql> create table t2 (c1 integer);
Query OK, 0 rows affected (1.60 sec)

mysql> insert into t1 values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.24 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into t2 values (1),(2),(4),(5);
Query OK, 4 rows affected (0.15 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select t1.c1,t2.c1 from t1 left join t2 on t1.c1=t2.c1 where t2.c1 is null;
+------+------+
| c1 | c1 |
+------+------+
| 3 | NULL |
+------+------+
1 row in set (0.10 sec)

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Re: getting unique records

Thanks very much Michael, that's exactly what i need.

"Michael Austin" wrote in message
news:rsm9h.2868$yf7.2170@newssvr21.news.prodigy.net...
> Dave wrote:
>
>> I'm in need of a little sql help.
>>
>> How do i get a list of ids from one table that do not have the same ids
>> in another table?
>>
>> I hope that makes sense.
>
> not exact syntax, but gives you an idea...
>
>
> select a.id, b.id from table1 a left join table2 b on a.id=b.id where b.id
> is null;
>
> mysql> create table t1 (c1 integer);
> Query OK, 0 rows affected (2.27 sec)
>
> mysql> create table t2 (c1 integer);
> Query OK, 0 rows affected (1.60 sec)
>
> mysql> insert into t1 values (1),(2),(3),(4),(5);
> Query OK, 5 rows affected (0.24 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> mysql> insert into t2 values (1),(2),(4),(5);
> Query OK, 4 rows affected (0.15 sec)
> Records: 4 Duplicates: 0 Warnings: 0
>
> mysql> select t1.c1,t2.c1 from t1 left join t2 on t1.c1=t2.c1 where t2.c1
> is null;
> +------+------+
> | c1 | c1 |
> +------+------+
> | 3 | NULL |
> +------+------+
> 1 row in set (0.10 sec)
>
> --
> Michael Austin.
> DBA Consultant
> Donations welcomed. Http://www.firstdbasource.com/donations.html
> :)