470,594 Members | 1,442 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

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


May 29 '06 #1
7 1232

"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
May 29 '06 #2
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.
May 29 '06 #3
Ike
"NOT IN "...that's it. I remember reading about that, wondering what anybody
would ever need that for....

Thanks! -Ike
May 29 '06 #4

"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
May 29 '06 #5
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.
May 30 '06 #6
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
May 30 '06 #7
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.
May 30 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

42 posts views Thread by Mike P. | last post: by
14 posts views Thread by Steve Jorgensen | last post: by
17 posts views Thread by so many sites so little time | last post: by
2 posts views Thread by Mucahit ikiz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.