Find rows where foreign key invalid 1

[RobBroekhuis]

I have a database with tables of the older (not innoDB) type where I want to set up some checks on data integrity. What's the easiest way to return rows in a second table (let's call it "species") whose foreign key (let's call it "genusid") does not exist as a primary key in a first table (let's call it "genus")?

In pseudo-mySQL, what I'm after is

select * from species where count(select * from genus where species.genusid=genus.genusid)=0

Thanks for any help - I suspect it's simple, but I've not gone much beyond simple inner joins...

Rob

 

[r937]

SELECT species.* 
  FROM species 
LEFT OUTER
  JOIN genus
    ON genus.genusid = species.genusid
 WHERE genus.genusid IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RobBroekhuis]

Excellent! Now I just need to read up on the syntax so I understand what's behind that!

Rob