One to Many join getting only 1 specific record from many table

[ManMountain]

Hi

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.

Many thanks

Ted.

[LifeinAsia]

Off the top of my head, i believe either of the following should work:
SELECT DISTINCT m.*
FROM MUSIC m INNER JOIN REVIEWS r ON m.uid=r.uid
WHERE r.thumbsup=1

SELECT *
FROM Music
WHERE uic IN (SELECT uic FROM Reviews WHERE thumbsup=1)

[brotherhood of LAN]

Not sure exactly what it is you're after... LifeinAsia's options will return all music that has a thumbsup in a review, but not music without a thumbs up

I'm not sure whether you're after that, or 'include reviews where possible and display all music anyway'

SELECT m.*,r.*
FROM music AS m
LEFT JOIN reviews AS r ON m.uid = r.uid AND r.thumbsup = 1
GROUP BY m.uid

The above would return all music, but r.* would have NULL values returned for music that has no reviews with thumbsup = 1.

I'd suggest having a reviewid column in the music table, and when someone thumbsupit you can update that value and join on it too.

[LifeinAsia]

Ooops, misread the OP- thought he only wanted music with 1 thumbs up.

[ManMountain]

Hi Folks

thanks for the input so far.

I don't actually need any review data at all. All I need is to know whether a MUSIC (records in music are pieces of music) has any thumbs up at all or not.

So something like

if music has thumbsup then do action. I don't need the reviews data, I don't need to know if it has more than one thumbsup. I only need to check if there are at least one thumbsup in the other table or not. If it finds one, it doesn't have to do anything else.

If there is a Thumbsup the field has value of 1, otherwise it has a value of zero.

So yes, maybe I wasn't quite clear enough - but I will be pulling ALL music records, then just need to check each music record against reviews to see if it has any thumbsup or not - true or false, not how many etc.

I don't know if B.O.L query will do the trick, I will give it a go and see what happens. It looks like it might.

Hopefully that is a bit clearer, but yes it is doing my head in. Any help would be appreciated.

Many thanks

Ted.

[ManMountain]

Actually as I was sitting in the dentist having my teeth drilled, I thought this might be more clear. So I am looking for something like this

SELECT MUSIC.*, (REVIEW WHERE ANY REVIEW.thumbsup = 1, OTHERWISE 0)

While $row[music]
output music data, if review==1 output icon

Maybe a MAX of review table would do the trick, since I am looking for any value of 1 and that is the highest value?

[ManMountain]

Hi

yes I tried it and with a bit of tinkering, BOL's solution worked or seems to work - so thanks for your input there guys.

One other minor thing - how would I tweak that query to then pull out a single or set of MUSIC records? Such as all MUSIC records where artist = 12? I've tried putting WHERE m.artist = '".$variable."' just before the ORDER BY, but it's then pulling out nothing. Have I got this in the wrong place, or is this the wrong way to do it?

Thanks again for your assistance this has been a great help.

All the best

Ted

[brotherhood of LAN]

That's right, your WHERE clause would go there. Can only assume that it matched no records because there was no match? Having just "WHERE 1" should match all records just to test.