SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to make this query?

    For a Band website I have to insert records into a table "release_lyrics":
    Code MySQL:
    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:
    Code MySQL:
    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.
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by donboe View Post
    So somehow I have to Join the tables release_tracks and release_lyrics but I have no idea how?
    i would join them on both columns -- release_id and track_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would join them on both columns -- release_id and track_id
    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
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •