# Searching for missing rows over three tables

**URL:** <https://forums.percona.com/t/searching-for-missing-rows-over-three-tables/867>\
**Category:** Other MySQL® Questions\
**Created:** [August 5, 2008, 11:58am UTC](https://forums.percona.com/t/searching-for-missing-rows-over-three-tables/867 "2008-08-05T11:58:11Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Carsten\_H](https://avatars.discourse-cdn.com/v4/letter/c/d26b3c/32.png) [@Carsten\_H](https://forums.percona.com/u/Carsten_H)\
**Post date:** [August 5, 2008, 11:58am UTC](https://forums.percona.com/t/searching-for-missing-rows-over-three-tables/867/1 "2008-08-05T11:58:11Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Carsten\_H](https://avatars.discourse-cdn.com/v4/letter/c/d26b3c/32.png) [@Carsten\_H](https://forums.percona.com/u/Carsten_H)\
**Post date:** [August 5, 2008, 12:03pm UTC](https://forums.percona.com/t/searching-for-missing-rows-over-three-tables/867/2 "2008-08-05T12:03:46Z")

</div>

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;