Searching for missing rows over three tables

MySQL & MariaDB Other MySQL® Questions
1

Hello!

I’ve three tables:

CREATE TABLE ram ( albID int(10) unsigned NOT NULL, medID int(10) unsigned NOT NULL, PRIMARY KEY (albID,medID)) ENGINE=InnoDB;

CREATE TABLE am ( artID int(10) unsigned NOT NULL default ‘0’, medID int(10) unsigned NOT NULL default ‘0’, PRIMARY KEY (artID,medID)) ENGINE=InnoDB

CREATE TABLE raa ( artID int(10) unsigned NOT NULL, albID int(10) unsigned NOT NULL, PRIMARY KEY (artID,albID)) ENGINE=InnoDB

Now I want a query, which gives me all “albID” from “ram”, where “albID” from “raa” is missing or where a row is totally missing in “raa”, giving a certain value for “artID”. I tried this query but it didn’t work:

SELECT ram.albIDFROM ramLEFT JOIN amON ram.medID = am.medIDLEFT JOIN raaON ram.albID = raa.albIDWHERE am.aID = 1234AND (raa.artist_id = am.aID OR raa.artist_id IS NULL)AND raa.album_id IS NULL;

Anyone has a better idea?

2

Well, I just got it. Put one WHERE statement into the ON statement:

SELECT ram.albIDFROM ramLEFT JOIN amON ram.medID = am.mIDLEFT JOIN raaON ram.albID = raa.albID AND raa.artID = am.aIDWHERE am.aID = 131486AND raa.albID IS NULL;