How to make this query?

Databases
1

For a Band website I have to insert records into a table “release_lyrics”:


CREATE TABLE IF NOT EXISTS `release_lyrics` (
  `lyric_id` smallint(2) NOT NULL auto_increment,
  `release_id` smallint(2) NOT NULL,
  `track_id` smallint(2) NOT NULL,
  `lyrics` text,
  PRIMARY KEY  (`lyric_id`),
  FOREIGN KEY (`release_id`) REFERENCES `releases` (`release_id`) ON DELETE CASCADE,
  FOREIGN KEY (`track_id`) REFERENCES `release_tracks` (`track_id`) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

The values for release_id and track_id are filled with data from the tables releases and release_tracks represented in drop down menus. The question I have is about the query populating the tracks drop down menu coming from the table release_tracks:


CREATE TABLE IF NOT EXISTS `release_tracks` (
  `track_id` smallint(2) NOT NULL auto_increment,
  `release_id` smallint(2) NOT NULL,
  `track_name` varchar(128) default NULL,
  `track_file` varchar(128) default NULL,
  PRIMARY KEY  (`track_id`),
  FOREIGN KEY (`release_id`) REFERENCES `releases` (`release_id`) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I need to make a query in such a way that, when I add other lyrics for the same album, the track_id(s) that is/are already in the table release_lyrics is/are not longer appearing in the tracks drop down menu. So somehow I have to Join the tables release_tracks and release_lyrics but I have no idea how?

Thank tou in advance.

2

i would join them on both columns – release_id and track_id

3

Hi Rudi. thank you for that but what next? I have it working with two separate queries but I have the feeling that this can be done in just one query? Any ideas would be highly appreciated.

As a matter of fact, I have nearly the same situation in a other project I’m working on, so It would be nice if I could solve this, which would mean I probable can use nearly the same approach for that one as well :slight_smile:

4

here’s an idea – use a LEFT OUTER JOIN and check one of the right table’s join columns for NULL, as this gives you the rows of the left table that have no matching row in the right table

:slight_smile: