SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Sequence help

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sequence help

    Trying to fine tune a database I created for a school project by adding a movie sequel column and what sequel the movie is, my two tables are as follows with just the necessities and some data for a visual:

    Code MySQL:
    CREATE TABLE movie (
    movieUPC char(15) NOT NULL PRIMARY KEY,
    movieTitle char(100) NOT NULL,
    movieParent char(15),
    movieSequence int(3)
    );
     
    CREATE TABLE movieNight (
    dateViewed datetime NOT NULL PRIMARY KEY,
    movieUPC char(15) NOT NULL
    );
     
    INSERT INTO movie
    VALUES
    ('024543088172', '28 Days Later', NULL, NULL),
    ('948569584378', 'Dog Day Afternoon', NULL, NULL),
    ('024543469902', '28 Weeks Later', '024543088172', 2),
    ('7348739745874', 'Exiled', NULL, NULL),
    ('025192213120', 'Back To The Future', NULL, NULL),
    ('025192213120a', 'Back To The Future II', '025192213120', 2),
    ('948569583496', 'Kiss Kiss, Bang Bang', NULL, NULL),
    ('025192213120b', 'Back To The Future III', '025192213120', 3)
    ;
     
    INSERT INTO movieNight
    VALUES
    ('2011-03-03 14:33:27', '7348739745874'),
    ('2011-03-23 16:01:13', '025192213120'),
    ('2011-03-24 12:01:13', '948569584378')
    ;

    How would I go about selecting movies that haven't been watched and selecting the parent movie or the next one in sequence if the first film has been watched? I know how to see what movies haven't been watched by left joining movie on movieNight and seeing if dateViewed is NULL, but now that I added the two new columns I'm not sure what I should do.
    Half way to nowhere

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still trying to figure this out and running queries looking at the results. This query makes me think that I don't need a movieSequence because the third movie in a series has no way to link to the second.

    Code MySQL:
    SELECT * FROM movie AS child 
    LEFT JOIN movie AS parent 
    ON child.movieParent = parent.movieUPC;

    After seeing the results the movieParent seems to make more sense if it was the movie before it in the series, not the first movie. Curious to any thoughts or suggestions.
    Half way to nowhere

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well solved my problem, I ended up needing the sequence column after all, since the first movie doesn't have a movie before it I needed it for my where clause to select the first movie and any sequels that fit the criteria.

    Code MySQL:
    select * from movie 
    LEFT JOIN movieNight 
    USING (movieUPC) 
    LEFT JOIN movieNight as sequel 
    ON sequel.movieUPC = movie.movieParent 
    WHERE movie.movieSequence = 1 
    AND movieNight.dateViewed IS NULL 
    OR sequel.dateViewed IS NOT NULL 
    AND movieNight.dateViewed IS NULL;

    I would love suggestions on maybe making it more compact, didn't know if there is shorthand for the where statement with the Null and not Null statements.
    Half way to nowhere


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
  •