Help with where clause

Dev Shed Forums Sponsor:

November 6th, 2012, 01:27 PM

annihilate

Contributing User

Join Date: Oct 2003

Location: Surrey, UK

Posts: 224

Time spent in forums: 1 Day 6 h 12 m 49 sec
Reputation Power: 10

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:

Original - 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

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:

Original - 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

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

__________________
-annihilate- Personal Site | Java Server Faces Discussion

November 6th, 2012, 03:27 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

November 6th, 2012, 04:19 PM

annihilate

Contributing User

Join Date: Oct 2003

Location: Surrey, UK

Posts: 224

Time spent in forums: 1 Day 6 h 12 m 49 sec
Reputation Power: 10

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.

November 6th, 2012, 05:18 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

run the subquery by itself... how many rows does it produce?

November 6th, 2012, 05:23 PM

annihilate

Contributing User

Join Date: Oct 2003

Location: Surrey, UK

Posts: 224

Time spent in forums: 1 Day 6 h 12 m 49 sec
Reputation Power: 10

Hi

I ran the following:

sql Code:

Original - sql Code
SELECT show_id, MAX(season) AS max_season FROM tv_seasons GROUP BY show_id

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.

November 6th, 2012, 05:26 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

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?

November 6th, 2012, 05:38 PM

annihilate

Contributing User

Join Date: Oct 2003

Location: Surrey, UK

Posts: 224

Time spent in forums: 1 Day 6 h 12 m 49 sec
Reputation Power: 10

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.

November 6th, 2012, 06:25 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

Quote:

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!

November 7th, 2012, 08:00 AM

annihilate

Contributing User

Join Date: Oct 2003

Location: Surrey, UK

Posts: 224

Time spent in forums: 1 Day 6 h 12 m 49 sec
Reputation Power: 10

Brilliant! That seems to be working perfectly.
Thanks a lot for your help.

Help with where clause

Developer Shed Advertisers and Affiliates