If I have a simple database with two tables, where tableB.crossid equals the
tableA.id, what kind of select statement would I create to select all
records in tableA that do not have their id as the crossid in tableB?
Thanks, Ike

CREATE TABLE `tableA` (
`id` int(11) NOT NULL auto_increment,
);
CREATE TABLE `tableB` (
`id` int(11) NOT NULL auto_increment,
`crossid` int(11) NOT NULL default '0'
);

"Ike" <rxv [at] hotmail.com> wrote in message
news:phJeg.12$F2.9 [at] newsread3.news.pas.earthlink.net...
> If I have a simple database with two tables, where tableB.crossid equals
the
> tableA.id, what kind of select statement would I create to select all
> records in tableA that do not have their id as the crossid in tableB?
> Thanks, Ike
>
> CREATE TABLE `tableA` (
> `id` int(11) NOT NULL auto_increment,
> );
> CREATE TABLE `tableB` (
> `id` int(11) NOT NULL auto_increment,
> `crossid` int(11) NOT NULL default '0'
> );
Join your two tables with a left outer join. Look at the result. See the
nulls?
In your where clause, deal with the nulls.Google "left outer join".

Rich

Ike wrote:
> what kind of select statement would I create to select all
> records in tableA that do not have their id as the crossid in tableB?

SELECT a.*
FROM tableA AS a LEFT JOIN tableB AS b ON a.id = b.crossid
WHERE b.crossid IS NULL

or as a subquery if you use MySQL 4.1 or higher:

SELECT a.*
FROM tableA AS a
WHERE a.id NOT IN (SELECT b.crossid FROM tableB AS b)

Regards,
Bill K.

"NOT IN "...that's it. I remember reading about that, wondering what anybody
would ever need that for....

Thanks! -Ike

"Bill Karwin" <bill [at] karwin.com> wrote in message
news:e5foam12vk5 [at] enews2.newsguy.com...
> Ike wrote:
> > what kind of select statement would I create to select all
> > records in tableA that do not have their id as the crossid in tableB?
>
> SELECT a.*
> FROM tableA AS a LEFT JOIN tableB AS b ON a.id = b.crossid
> WHERE b.crossid IS NULL
>
> or as a subquery if you use MySQL 4.1 or higher:
>
> SELECT a.*
> FROM tableA AS a
> WHERE a.id NOT IN (SELECT b.crossid FROM tableB AS b)
>
> Regards,
> Bill K.

Bill,

Sometimes it is better to give hints, clues, and guidance. They will learn
more.

Rich

Rich Ryan wrote:
> Sometimes it is better to give hints, clues, and guidance. They will learn
> more.

True, sometimes. But it's also valid to teach by showing examples.

I find that the people who can extrapolate patterns do, and the people
who don't, don't.

Regards,
Bill K.

Ouch....I just realised however that the two tables each reside in separate
MySQL DBs. I'm wondering now if this is even possible? -Ike

Ike wrote:
> Ouch....I just realised however that the two tables each reside in separate
> MySQL DBs. I'm wondering now if this is even possible? -Ike

MySQL permits the syntax of dbname.tablename, as long as both databases
reside on the same server.

E.g.:

SELECT t1.*, t2.* ...
FROM db1.table1 AS t1 JOIN db2.table2 AS t2 ON ...
WHERE ...

Regards,
Bill K.