Help with query

[jasongr]

Hi people

I have been struggling with the following query for quite some time now. I hope someone could help me

I am storing albums and their content in my database.
Each album is identified by and ID (column Album_ID in table entry_in_album)
Table entry_in_album maps albums to the entries in them. It is a n-n relationship.

CREATE TABLE `entry_in_album` (
`Album_ID` int unsigned not null,
`Entry_ID` int unsigned not null,
PRIMARY KEY (`Album_ID`, `Entry_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have an additional table that stores data on the actual entries in the album:

CREATE TABLE `entry_info` (
`Entry_ID` int unsigned not null,
`Type` varchar(255) not null,
PRIMARY KEY (`Entry_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

An entry can be one of 2 types: 'photo' or 'video'

Given a set of albums IDs, I need to extract the following information:
for each album, get the number of photos in that album and the number of videos

I tried the following with a single album:

SELECT COUNT(entry_info.Entry_ID) NumEntries
FROM (entry_info, entry_in_album)
WHERE entry_in_album.Album_ID = 5 AND
entry_info.Type = 'photo' AND
entry_in_album.Entry_ID = entry_info.Entry_ID
UNION
SELECT COUNT(entry_info.Entry_ID) NumEntries
FROM (entry_info, entry_in_album)
WHERE entry_in_album.Album_ID = 5 AND
entry_info.Type = 'video' AND
entry_in_album.Entry_ID = entry_info.Entry_ID

This is a sample query that returns the number of items for album number 5.

The problem with this solution is that it returns 2 rows in the results set (because of the usage of UNION).
For example:

NumEntries
14
3

I would like it to return 1 row with 2 columns (NumPhotos and NumVideos):

NumPhotos NumVideos
14 3

Once I can get it to return 2 columns (without using UNION), I could have it return the Album_ID and then use it to return the data on multiple albums, and not just one.
Like so:

Album_ID NumPhotos NumVideos
5 14 3
2 11 0

any suggestions?

[chazzy]

You need to use nested sql queries like this:

SELECT a.Album_ID, (SELECT COUNT(*) from entry_info e1 WHERE a.Entry_ID = e1.Entry_ID and type='photo') NumPhotos, (SELECT COUNT(*) from entry_info e2 WHERE a.Entry_ID = e1.Entry_ID and type='video') NumVideos FROM entry_in_album a;

If you're using MySQL, this will only work in version 4.1 and higher.

[jasongr]

here is a solution that I got from Guelphdad:

SELECT album_id,SUM(CASE WHEN entry_info.type='photo' THEN 1 ELSE 0 END) AS Photos,SUM(CASE WHEN entry_info.type='video' THEN 1 ELSE 0 END) AS VideosFROMentry_in_albumLEFT JOINentry_info ONentry_in_album.entry_id = entry_info.entry_idGROUP BY album_id


thanks for your solution