Help with query

[unknown101]

 

Hi Guys,

 

im trying to workout how to write a query which involves the following table example:

 

ArtistID || TrackID || Rating || UserID

2                    1              3                1

2                    2              3                1

2                    3              2                1

 

This table will have many ratings, but how would I select all artistIDs which have been rated more than 3 times, by the same user and consistenly giving them a rating of 3 or below (as the table above demonstrates).  So for example the only information i have is the user ID, i then need to find out that info - could anyone give me a hand please?

 

Any help appreciated..

 

Cheers

 

 

[rhodesa]

hum...not tested, but try this:

SELECT ArtistID WHERE Rating <= 3 AND UserID = '1' AND count(Rating) > 3 GROUP BY ArtistID

[GingerRobot]

You'll need the HAVING clause:

 

SELECT ArtistID,count(*) as count FROM yourtable WHERE Rating <= 3 AND UserID=1 GROUP BY ArtistID HAVING count >=3

[unknown101]

Thanks for the help guys, will test it shortly:)