Announcement

Announcement Module
Collapse
No announcement yet.

Searching for missing rows over three tables

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Searching for missing rows over three tables

    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;

    Comment

    Working...
    X