Subquery Problem

[Matrixkid]

Hi there,

 

I am having trouble getting the results I want.

I am trying to find out basically what songs I played this week but not last.

 

Here is a table example:

Song Title  |  Week Play
------------------------
Song  1     |  41
Song  2     |  41
Song  3     |  41
Song  4     |  41
Song  1     |  40
Song  3     |  40

 

So my query result would be Song 2 and Song 4 - since they were not played in week 40.

 

ive tried:

SELECT * FROM `music` WHERE 'week' = 41 AND NOT EXISTS
(SELECT * FROM 'music' WHERE `week` = 40)

 

thanks for the help.

MK

[wastedthelight]

You should post the sql you have so far.

 

You'll wanna use this though.

 

WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

 

EDIT: Looks like you typed your SQL as I was replying. ha. but yeah I'd switch it around or you'd have to change your week number every week or have an input box so you can "what week do you want to check". However my way will automatically do it for you but you'd only see the last 7 days and couldn't back track.    So you could setup so when you submit a song it puts the date (sql format) in a new dat field.

 

I actually do almost exacly what you want with the following code.  The music director selects a genre to check (radio station) and then the php fills in the sql query with the correct thing to check.

 

 

$playlist = mysql_query("SELECT artist, album, section, sectionnumber, date, COUNT(sectionnumber) AS timesplayed FROM playlist WHERE section = '$getgenre' AND sectionnumber > $getcatnumber AND date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY section, sectionnumber ORDER BY section ASC, timesplayed DESC");

 

The $getgenre is gotten from a form input that tells the script what to get. You'd want to do date instead.

 

Good luck.

[Matrixkid]

Thanks for the help.

 

All I have in the table is "Song name" and "Week number" in a table called music.

 

I tried this:

SELECT * FROM music WHERE 'week' = 41 AND NOT EXISTS
(SELECT * FROM music WHERE 'week' = 40)

 

I can change the week numbers myself, its not an issue.

 

it is error free but returns zero results.

[Matrixkid]

I cant edit my post, but I got the query.

 

SELECT * FROM music WHERE song NOT IN (SELECT song FROM music WHERE week = 40)

 

 

Cheers!

[wastedthelight]

I cant edit my post, but I got the query.

 

SELECT * FROM music WHERE song NOT IN (SELECT song FROM music WHERE week = 40)

 

 

Cheers!

 

Wouldn't that grab weeks below 40? and above?  I think you want where week > 40 so it gets week 41, change > 41 to get week 42.

[Barand]

$currentWeek = 41;

$sql = "SELECT song FROM music
          WHERE week = $currentWeek
          AND song NOT IN (SELECT song FROM music WHERE week= $currenWeek-1 )";

 

Do the weeks in the second year start again at 1? If they do you have a problem in week 1 next year.

 

It would be better to hold

 

song    |  date_played

-------+-------------

Song 1 | 2008-10-01

Song 1 | 2008-10-02

[fenway]

Can't that be written as a left join? NOT IN is bad for performance.

[Barand]

Like this?

 

$sql = "SELECT song FROM music m
          LEFT JOIN music as x ON m.song = x.song AND x.week = $currentWeek-1
          WHERE m.week = $currentWeek     
          AND x.song IS NULL";

[fenway]

Yup.