SELECT not in

[Matrixkid]

Hi there,

 

I need help constructing a select statement that in short selects songs from a database that have not been stored in any prior weeks or years.

 

Below is the table:

artist   |    song  |  weeknum  |    yearnum
lg            ab           49       2009
jvc           cd           49       2009
sony          ef           49       2009
lg            ab           31       2009
song          ef           22       2008

 

Lets assume the current week is 49.

From the above example, my desired result would be jvc.

Im trying to select all songs that were stored in week 49 of this year and never before. So, anything less than week 49, or any year less than 2009.

 

I had this statement working before but because of the amount of data it no longer works.

 

SELECT Count( song ) AS CountSong, artist , song FROM TESTDB 
WHERE weeknum =49 AND yearnum = 2009 AND
song NOT IN (SELECT song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009) 
GROUP BY song ORDER BY `CountSong` DESC

 

I welcome all advice and opinions.

I am aware that the database structure isnt the greatest. Anything to make this sort of select statement work and i'll listen.

 

Thanks

MK

[rajivgonsalves]

you do not require the not in, since its the same table you can directly put it in the main query

[Matrixkid]

you do not require the not in, since its the same table you can directly put it in the main query

 

how would i got about constructing that statement?

thanks for the help

[rajivgonsalves]

you already have ? just remove the song not In ....

[Matrixkid]

you already have ? just remove the song not In ....

 

I think you misunderstood what results im looking for.

If i remove the not in part I am selecting all songs from week 49, in year 2009.

I want songs only that appeared in week 49, year 2009 and NEVER before.

 

thanks

MK

[fenway]

Ah... then you need a LEFT JOIN, or NOT EXISTS.

[Matrixkid]

Im trying the NOT EXISTS but keep getting 0 results.

 

SELECT Count(song) AS CountSong, artist , song FROM TESTDB
WHERE weeknum = 49 AND yearnum = 2009 AND
NOT EXISTS (SELECT artist,song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009 GROUP BY song)
GROUP BY song ORDER BY `CountSong` DESC

[Zane]

SELECT * FROM TESTDB WHERE NOT EXISTS ((
     SELECT DISTINCT song FROM TESTDB WHERE weeknum ) as songCheck)

 

might work?

[Matrixkid]

Affected rows: 0
Time: 0.032ms

 

Nope. Should return 1 row, JVC (from the table in the first post).

 

If I change NOT EXISTS to EXISTS, it gives me the opposite results. All the songs played prior to week 49. But as soon as I make it NOT then it returns nothing.

[Zane]

try using NOT IN with it and see what happens.  The fact that it showed the opposite means we're getting close

 

Perhaps this revision

SELECT * FROM TESTDB WHERE song NOT IN ((
     SELECT DISTINCT song FROM TESTDB WHERE weeknum ) as songCheck)

[Matrixkid]

thanks for the help Zanus, I appreciate it.

 

Unfortunately the revision doesnt work either. I have to remove the ) as songCheck part as it returns an error.

 

This statement returns no results:

SELECT * FROM TESTDB WHERE song NOT IN (
     SELECT DISTINCT song FROM TESTDB WHERE weeknum <= 49 AND yearnum <= 2009
)

 

This statement returns the correct result:

SELECT * FROM TESTDB WHERE song NOT IN (
     SELECT DISTINCT song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009
)

 

I can run the NOT IN on the TESTDB and get the results i need, but as soon as I apply the query to my active db the query lags and goes nowhere. The database has over 190,000 records and grows every 3 hours.

 

heres the error: [Err] 1053 - Server shutdown in progress

 

Im not really sure where to go from here.

 

Thanks again.

MK