#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Surrey, UK
    Posts
    224
    Rep Power
    12

    Help with where clause


    Hi

    I am struggling to work out how to write a specific query. This is what I have so far:

    sql Code:
    SELECT n.show_id, n.show_name, n.STATUS, 
    MAX(s.season) AS max_season, s.date_added AS max_season_added 
    FROM tv_show_names AS n 
    LEFT JOIN tv_seasons AS s ON s.show_id = n.show_id 
    WHERE n.STATUS != 'END' 
    GROUP BY n.show_id 
    ORDER BY n.show_name

    This seems to be showing what I expect. i.e. it is showing all TV shows where the status is not END and its showing the highest season number for that show and the date that season was added.

    What I want to do is restrict the results to only show TV shows where the date added of the highest season number for that show is more than a year ago. So I tried the following but this is not giving the required results.

    sql Code:
    SELECT n.show_id, n.show_name, n.STATUS, 
    MAX(s.season) AS max_season, s.date_added AS max_season_added 
    FROM tv_show_names AS n 
    LEFT JOIN tv_seasons AS s ON s.show_id = n.show_id 
    WHERE n.STATUS != 'END' 
    AND s.date_added <= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 YEAR) 
    GROUP BY n.show_id 
    ORDER BY n.show_name

    The MySQL version is 5.1.65.

    Could anyone please give some assistance on this query?

    Thanks
    Colin
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Code:
    SELECT n.show_id
         , n.show_name
         , n.STATUS
         , m.max_season
         , s.date_added AS max_season_added 
      FROM tv_show_names AS n 
    INNER 
      JOIN ( SELECT show_id
                  , MAX(season) AS max_season
               FROM tv_seasons
             GROUP
                 BY show_id ) AS m
    INNER
      JOIN tv_seasons AS s 
        ON s.show_id = m.show_id
       AND s.season = m.max_season
       AND s.date_added <= CURRENT_DATE - INTERVAL 1 YEAR 
     WHERE n.STATUS != 'END' 
    ORDER 
        BY n.show_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Surrey, UK
    Posts
    224
    Rep Power
    12
    Hi

    Thanks for replying.

    I tried running that query and got quite lot more rows back than expected. There are only 57 TV shows where the status is not END. The query returned over 6000 rows.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    run the subquery by itself... how many rows does it produce?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Surrey, UK
    Posts
    224
    Rep Power
    12
    Hi

    I ran the following:
    sql Code:
    SELECT show_id, MAX(season) AS max_season 
    FROM tv_seasons 
    GROUP BY show_id

    This produced 162 rows. There are 162 rows in the tv_show_names table, so I believe this is the expected result, since every show does have at least 1 season in the tv_seasons table.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    so far so good...

    now take the query that i gave you, and remove the last INNER JOIN (as well as s.date_added from the SELECT list), like this --
    Code:
    SELECT n.show_id
         , n.show_name
         , n.STATUS
         , m.max_season
      FROM tv_show_names AS n 
    INNER 
      JOIN ( SELECT show_id
                  , MAX(season) AS max_season
               FROM tv_seasons
             GROUP
                 BY show_id ) AS m
     WHERE n.STATUS != 'END' 
    ORDER 
        BY n.show_name
    now how many rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Surrey, UK
    Posts
    224
    Rep Power
    12
    This is where it goes wrong.
    9454 rows

    Which looks like its 58 x 163 (there are now 58 shows not with a status of END and there are 163 shows in total)
    Last edited by annihilate; November 6th, 2012 at 05:43 PM.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by annihilate
    This is where it goes wrong.
    9454 rows
    well, well

    it seems i am an idiot

    try this --
    Code:
    SELECT n.show_id
         , n.show_name
         , n.STATUS
         , m.max_season
         , s.date_added AS max_season_added 
      FROM tv_show_names AS n 
    INNER 
      JOIN ( SELECT show_id
                  , MAX(season) AS max_season
               FROM tv_seasons
             GROUP
                 BY show_id ) AS m
        ON m.show_id = n.show_id
    INNER
      JOIN tv_seasons AS s 
        ON s.show_id = m.show_id
       AND s.season = m.max_season
       AND s.date_added <= CURRENT_DATE - INTERVAL 1 YEAR 
     WHERE n.STATUS != 'END' 
    ORDER 
        BY n.show_name

    Comments on this post

    • annihilate agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Surrey, UK
    Posts
    224
    Rep Power
    12
    Brilliant! That seems to be working perfectly.
    Thanks a lot for your help.

IMN logo majestic logo threadwatch logo seochat tools logo