[Techtalk] SQL query question

Rachel McConnell rachel at xtreme.com
Tue Apr 29 07:04:07 UTC 2008


Hi Walt,

You want a 'left' or 'full' join, and then select the rows where a known
table 2 value is null.  Here's an example:

create table tableOne (
  idOne int UNIQUE KEY,
  name varchar(40)
);

create table tableTwo (
  idTwo int UNIQUE KEY,
  idOne int,
  name varchar(40)
);

The values from the field, idOne, in both tables, match up.  To get all
the rows from Table 1 that don't have corresponding rows in Table 2
(based on the idOne column), use this SQL:

 select * from tableOne as t1 left join tableTwo as t2 on t1.idOne =
 t2.idOne where t2.idTwo is null;

The left join syntax indicates that ALL rows of the 'left side' of the
joined tables will be shown, instead of only the ones that match values
in the second table.

You could also do,

 select * from tableOne as t1 left join tableTwo as t2 on t1.name =
 t2.name where t2.name is null;

This will give you a potentially different disunion, depending on your
data.

Depending on the database you're using, the syntax may vary slightly. 
If you're working with MySQL, what I gave should work exactly as written
- except that I have not added any test data, so no rows will be
returned.

Hopefully this makes sense!

Rachel

On Mon, 28 Apr 2008 11:52:25 -0400, "Walt" <pippin at freeshell.org> said:
> I'm not sure if this is even possible with SQL, but I'm
> hoping it is...
> 
> I have to tables and want to select all the entries from
> Table 1 that do not appear in Table 2. So far I've only
> been able to successfully select the entries that appear
> in both tables, which is helpful if I end up needing to
> delete them by hand, but will be a bit time-consuming...
> 
> I've tried doing some searching, but unfortunately don't
> even know what command or terms to search for.
> 
> Thanks for any help!
> Walt
> 
> -~
> 
> 
>      Freedom of the press is the reliable reasonable and moral
>      nature of freedom.  The character of the censored press
>      is the nondescript confusion of tyranny.
>                          Karl Marx 
> 
> _______________________________________________
> Techtalk mailing list
> Techtalk at linuxchix.org
> http://mailman.linuxchix.org/mailman/listinfo/techtalk


More information about the Techtalk mailing list