Cant get my mind around this simple query

[Ike]

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'
);

[Rich Ryan]

"Ike" <rx*@hotmail.com> wrote in message
news:ph***********@newsread3.news.pas.earthlink.ne t...

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

[Bill Karwin]

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.

[Ike]

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

Thanks! -Ike

[Rich Ryan]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:e5*********@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

[Bill Karwin]

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.

[Ike]

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

[Bill Karwin]

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.