RecordCount & Grouping, I think?

[neilsytner]

Hi all,

I'm hoping someone here will be able to help me out with a little query that
I've not been able to get working (and I've asked on two other CF forums).

What I would like to achieve is to do a recordcount of the photo's that are
contained within each album. The current Access DB contains the following two
tables:

tbl_albums:

album_id | album_title | album_description

tbl_photos:

photo_id | photo_album | ...

What exactly does the query need to be? This is what I've tried so far, but
it's not quite working:

<cfquery name="q_get_albums_item" datasource="#application.dsn#">
SELECT tbl_albums.album_id, tbl_albums.album_title, count(tbl_photos.photo_id)
AS photo_count
FROM tbl_albums, tbl_photos
WHERE tbl_albums.album_id = tbl_photos.photo_album
GROUP BY tbl_albums.album_id, tbl_albums.album_title
</cfquery>

The result of the above is the same number when used as shown below:

<table border="0" cellpadding="0" cellspacing="5" width="600">
<tr>
<th>Album</th>
<th>Description</th>
<th>Photos</th>
</tr>
<cfloop query="q_get_albums">
<tr valign="top">
<td><p><a
href="photos.cfm?aid=<cfoutput>#album_id#</cfoutput>"><cfoutput>#album_title#</c
foutput></a></p></td>
<td><p><cfoutput>#album_description#</cfoutput></p></td>
<td><p><cfoutput>#q_get_albums_item.photo_count# </cfoutput></p></td>
</tr>
</cfloop>
</table>

I hope someone can point me in the right direction!

Many thanks in advance for any replies received.

[vkunirs]

Hi

Can you post your error please.?

I think in the query you have missed the dexcription field, is that is the
error you are getting.

SELECT tbl_albums.album_id, tbl_albums.album_title,album_description,
count(tbl_photos.photo_id) AS photo_count
FROM tbl_albums, tbl_photos
WHERE tbl_albums.album_id = tbl_photos.photo_album
GROUP BY tbl_albums.album_id, tbl_albums.album_title,album_description


try to post your error if it not the above.

[MikerRoo]

You didn't say what was wrong with the current query.

Anyway, the attached version might work better for you.

-- MikeR



<CFQUERY name="q_get_albums_item" datasource="#application.dsn#">
SELECT
tbl_albums.album_id,
tbl_albums.album_title,
COUNT (tbl_photos.photo_id) AS photo_count
FROM
tbl_albums
LEFT JOIN
tbl_photos ON tbl_photos.photo_album = tbl_albums.album_id
GROUP BY
tbl_albums.album_id,
tbl_albums.album_title
ORDER BY
tbl_albums.album_title
</CFQUERY>

[neilsytner]

there's no cf error - it just displays the record count as 18 for each album,
and there's actually 4, 10 & 18 in each respective category.

i'll try out both of your suggestions.

thanks for the replies :)

[neilsytner]

OK MikerRoo - your query did the job! many thanks!

could you explain to me why it worked, so that i can now learn how the query
worked? this would really help me (and others i'm sure) for future reference.

much appreciated.

[MikerRoo]

OK mark this question as answered and I'll give a brief rundown (it's past my
bedtime).

Also Access is quirky and I haven't used it for a long time, so I may get some
of the details slightly off...

Anyway, your code would probably work if you defined a relationship in Access
(Primary key, Foreign key index).

If you DON'T have a built in relationship, Access queries seem to need an
explicit join (the join statment). You can change the "LEFT JOIN" to "INNER
JOIN" and it will work with your current data set.

But suppose you add a new album and it doesn't yet have any photos. Your
original code and the "inner join" would not fetch that album at all.

Left join includes it and the count yields zero just like it should.

So, you can look up joins and learn more.

I suggest you also look up relationships in Access help and build them up in
your table. Relationships and indexes speed things up and stop bad data
before it can be entered.

Cheers,
-- MikeR

[neilsytner]

thanks mikeroo - your answer, explantion and time are all great appreciated! i shall mark this as answered :)