Jump to content

SELECT not in


Matrixkid

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/183938-select-not-in/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/183938-select-not-in/#findComment-971363
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/183938-select-not-in/#findComment-971669
Share on other sites

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)

Link to comment
https://forums.phpfreaks.com/topic/183938-select-not-in/#findComment-971675
Share on other sites

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

 

Link to comment
https://forums.phpfreaks.com/topic/183938-select-not-in/#findComment-971682
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.