help - accounting for null records

Anon

The following query (when used with the rest of my code) returns a band name, album name, review page link (using reviews.id), and the total number of comments made by users to date. However, if no comments have been made, it does not return that record at all. Can I alter this query to allow for those null records, or will this need to be specified elsewhere in the code? If so, how? I'm fairly new and could not find a solution in the manual or on the boards.

select reviews.id, reviews.band, reviews.album, count(reviewcomments.review) as revcount from reviews, reviewcomments where reviews.id = reviewcomments.review group by reviews.id, reviews.band, reviews.album

Much thanks in advance if anyone can help!

rrijnders

What you are talking about is that there is no record in the second table of your join. This is not a NULL value, it is a NON-MATCHING record.

By default, the type of join you are perfoming is an "inside join". That means that if there is no matching record, you get no row returned. What you want to do is a "LEFT JOIN". This will return the columns from the first table even if there is no matching record in the second table.
<font color=blue>
SELECT reviews.id, reviews.band, reviews.album, count(reviewcomments.review) as revcount
FROM reviews
LEFT JOIN reviewcomments ON reviews.id=reviewcomments.review
GROUP BY reviews.id, reviews.band, reviews.album
</font>

BTW, your query looks IBM-Like; are you an AS/400 or RS/6000 coder?

-- Rich

Anon

Rich - Thanks! I had played around with joins but gave up when they didn't work...

You rock!

Oh, and no, I'm not a coder by trade in any form - I've picked things up from mainframers and SQL-lovers at work, so no doubt that's where my IBM-ness comes from 🙂

Thanks so much for the help.