Getting content and count from SQL at same time

Question

I've got 2 tables, albums and pictures...

pictures has relation to albums via fk_albumID.

Now want im trying is to select all from albums, and at the same time count how many pictures that has relation to albums...

I tried with: SELECT *, (SELECT COUNT(*) FROM pictures WHERE pictures.fk_albumID = albums.albumID) AS albumCount FROM pictures, albums

But this first of all dont return any results if theres no pictures at all... And then it repeats results according to count. So if albums has 3 pictures, then i will get the album 3 times in my list, when i bind it to a Repeater.

And i tried: SELECT COUNT(albums.albumID) AS albumCount, albums.albumName, albums.albumID FROM albums INNER JOIN pictures ON pictures.fk_albumID = albums.albumID GROUP BY albums.albumID, albums.albuName

But this only shows albums that has pictures...

Changed up some names in 2nd query... `album_id` is now `albumID`, `billeder` is now `pictures`, `album_name` is now `albumName, `album_home` is now `fk_albumID`, AlbumSize is now albumCount — Christian Bekker, Oct 10 '12 at 11:06

score 2 · Accepted Answer · answered Oct 10 '12 at 10:36

2

You were close though. All you need is go from an INNER JOIN to an OUTER JOIN

SELECT COUNT(billeder.album_id) AS AlbumSize, 
       albums.album_name, 
       albums.album_id 
  FROM albums 
 LEFT OUTER JOIN billeder 
              ON billeder.album_home = albums.album_id 
 GROUP BY albums.album_id, albums.album_name

answered Oct 10 '12 at 10:36

deroby

5,902
2
19
33

Just renamed some stuff... I use it like this: `SELECT COUNT(pictures.fk_albumID) AS albumCount, albums.albumName, albums.albumInfo, albums.albumID FROM albums LEFT OUTER JOIN pictures ON pictures.fk_albumID = albums.albumID GROUP BY albums.albumID, albums.albumName, albums.albumInfo` But i get error when i try to fill my adapter with my datatable... `The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator` – Christian Bekker Oct 10 '12 at 10:58
Okay when i only try to Eval the `albums.albumName` out it works fine... but if i add to get `albumInfo` and `albumDate`, then it gets same error...: `SELECT COUNT(pictures.fk_albumID) AS albumCount, albums.albumName, albums.albumInfo, albums.albumDate, albums.albumID FROM albums LEFT OUTER JOIN pictures ON pictures.fk_albumID = albums.albumID GROUP BY albums.albumID, albums.albumName, albums.albumInfo, albums.albumDate` – Christian Bekker Oct 10 '12 at 11:12
Going from the error it seems that you created albumInfo as an (n)text field. Although they are still supported on SQL2k8 there is very little (if any?) reason to use them. Either define the field as a (n)varchar(x) field where x smaller than 8000 in case of varchar and 4000 in case of nvarchar. Or if you fear you'll need more than that you can make them (n)varchar(max) which comes down to a string of about 2Gb. Mind that (max) has some overhead which you may rather stay away from when not strictly needing it. – deroby Oct 10 '12 at 11:22
I used a normal Text... changed to varchar, and problem was solved! thanks! Btw. any specific reason why text is bad to use? (except from the error i got :P) – Christian Bekker Oct 10 '12 at 11:34
1

text and image are 'old school' =) In the past (read : pre MSSQL 2005) they were the only way to store 'unlimited' amounts of data in the database. You had to use special ways to read and write them as they were only accessible in blocks of 8k. The 'new' (n)varchar(max) and varbinary(max) types can be used like any other datatype making them a lot easier to work with as you can access them directly and completely using variables of the same type. But like I said, keep in mind that they still come with some technical limitations that makes them '2nd best' to 'non-max' varchar or binary columns. – deroby Oct 10 '12 at 12:49

score 1 · Answer 2 · answered Oct 10 '12 at 10:21

You can use a sub-query:

SELECT p.PicCount AS AlbumSize, 
  albums.album_name, 
  albums.album_id 
FROM albums a
INNER JOIN billeder b
  ON b.album_home = a.album_id 
LEFT JOIN 
(
  SELECT count(*) PicCount, fk_albumid
  FROM pictures
  GROUY BY fk_albumid
) p
  on a.album_id = p.fk_albumid

score 1 · Answer 3 · answered Oct 10 '12 at 10:26

1

SELECT t1.album_title, isnull(sum(flag),0) AS PicCount
FROM albums AS t1
LEFT JOIN
(
SELECT *, 1 AS Flag
FROM pictures) AS t2
ON t1.fk_albumID = t2.fk_albumID
GROUP BY t1.album_title

Link to SQL Fiddle

answered Oct 10 '12 at 10:26

Gidil

4,137
2
34
50

score 0 · Answer 4 · answered Oct 10 '12 at 10:19

0

SELECT (SELECT COUNT(1)
        FROM billeder
        WHERE billeder.album_home = albums.album_id) albumCount,
       albums.album_name,
       albums.album_id
FROM albums

answered Oct 10 '12 at 10:19

RichardTheKiwi

105,798
26
196
262

score 0 · Answer 5 · answered Oct 10 '12 at 10:32

0

select 
    AL.album_name, 
    AL.albumID, 
    (select count(*) from 
            ALBUMS AL1, 
            PICTURES PIC1 
     where  AL1.albumID = PIC1.albumID) as CNT_ALBUMID
from 
    ALBUMS AL

Have a try

answered Oct 10 '12 at 10:32

diaryfolio

605
10
24

Getting content and count from SQL at same time

5 Answers5