I am quite new at SQL statements and I have never been very good at joins etc. I am having a problem working out how to do the following:
I have 2 tables, one called MUSIC, one called REVIEWS. The two tables link on a field called uid - records in MUSIC are unique, but there may be many reviews for each MUSIC record.
REVIEWS has a field called thumbsup, which is set to value 1 if a user gives a MUSIC the thumbs up. Otherwise, the field is zero. There may be many thumbsup in REVIEWS, one thumbsup, or no thumbsup.
I am trying to display a list of records in MUSIC, and if there is a corresponding REVIEW record that contains a thumbsup value of 1 - just one record in REVIEWS will do it - then to display an icon on the list page.
I have had a good look around various similar questions but nothing quite like this comes up. I have tried doctoring around similar kinds of joins, but frankly how it all works is beyond me. Sorry for being an idiot.
By way of evidence that I have at least have a go, so far I have come up with this based on another post:
SELECT m.*, r.thumbsup FROM (SELECT MAX(thumbsup) uid FROM reviews GROUP By uid) maxThumbs INNER JOIN reviews r ON maxThumbs.uid = reviews.uid INNER JOIN music m ON music.uid = reviews.uid
Needless to say it doesn't work :(
If anyone could help me out here with the right query structure I would be extremely grateful.