Join to add a column with a count from another table? 1

[ShawnMolloy]

Hello,

I have two tables.

Image_Album
album_Id int

Images
album_Id int


I want to get a query that returns all the Image Albums, and a column with the count of row that have the album_id in the images table. What is the best way to acomplish that?

Here is my current query; whcih doesn't work :S

SELECT A.AlbumDescA.,DateCreated,A.UserId,A.IsPrivate,A.CoverImgSrc,A.AlbumName,
(SELECT count(*) from image B where B.AlbumId = A.AlbumId) ImageCount
FROM Image_Album
WHERE USERID=@userid


Any help would be greatly appricated!

Thanks,

-- shawn

 

[PHV]

Perhaps this ?
SELECT A.AlbumDesc,A.DateCreated,A.UserId,A.IsPrivate,A.CoverImgSrc,A.AlbumName,COUNT(*) ImageCount
FROM Image_Album A INNER JOIN Images B ON A.AlbumId = B.AlbumId
WHERE A.UserId=@userid
GROUP BY A.AlbumDesc,A.DateCreated,A.UserId,A.IsPrivate,A.CoverImgSrc,A.AlbumName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ShawnMolloy]

Thank you for your reply! That seems like it would work. When I plug it in I get this Error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The column AlbumDesc is of the datatype NTEXT.

Can you please explain to me why the GROUP BY clause is requried in this query? I have used many inner joins but never had to employ this clause.

So, I assume I have to add a IS NOT NULL to compare to the NTEXT column??

Thanks!!!!

-- shawn

 

[PHV]

OK, back to your original idea:
SELECT A.AlbumDesc,A.DateCreated,A.UserId,A.IsPrivate,A.CoverImgSrc,A.AlbumName,
(SELECT count(*) from image[!]s[/!] B where B.AlbumId = A.AlbumId) ImageCount
FROM Image_Album [!]A[/!]
WHERE USERID=@userid

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ShawnMolloy]

Yep that worked perfect. Just a simple subquery. Thanks!!

 

[PHV]

Can you please explain to me why the GROUP BY clause is requried in this query
I showed you an aggregate query.
BTW, what type of data is NTEXT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ShawnMolloy]

ntext is what I usually use for text that can be really really long. I used to use TEXT, but I ran into some problems with special characters getting truncated and replaced with the non intended character so I assuem NTEXT is some kind of unicode version of text.

Is there another option for long strings of text?

 

[cjlebel]

Your original statement should work fine, with a few minor tweaks (you forgot an "s" and "A.AlbumDescA.,"

SELECT
A.AlbumDesc,DateCreated,A.UserId,
A.IsPrivate,A.CoverImgSrc,A.AlbumName,
(SELECT COUNT(Id)
FROM images B
WHERE B.album_Id = A.album_Id) AS ImageCount
FROM Image_Album A
WHERE A.USERID=@userid