December 01, 2020, 08:49:10 pm
News: If someone gives you crap then give them a Bad Star
Pages: [1]   Go Down
Author Topic: JOINING another table with a GROUP BY ... HAVING query?  (Read 2953 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: January 14, 2008, 11:51:39 am »

I have two tables one table lists a file directory path and gives it an ID number. The other table associates a particular file with the directory path in the first table by using the ID number. What I want to do is look into the second table and find all the number of times the IDs are used and return not only the id number but also the directory path found in the second table.

I already know that if I do
Code: (sql)
SELECT directory_id as dir_id, COUNT(directory_id) as fileCnt
FROM storage_files
GROUP BY directory_id
HAVING (COUNT(directory_id) > 0)
ORDER BY directory_id;
I will get the number of times that files are assigned to a particular directory_id. However, it doesn't give me the directory_name that is found in the other table.

I did try this
Code: (sql)
SELECT sf.directory_id as dir_id, COUNT(sf.directory_id) as fileCnt, sd.directory_name as dir_name
FROM storage_files as sf
JOIN storage_directories as sd
ON sf.directory_id = sd.directory_id
GROUP BY sf.directory_id
HAVING (COUNT(sf.directory_id) > 0);

But I get the error
Quote
Msg 8120, Level 16, State 1, Line 1
Column 'storage_directories.directory_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So is there a way to get the data I want in One query or will I need more than one?
Logged

-- signature --

Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #1 on: January 14, 2008, 12:26:04 pm »

Have you just tried adding the sd.directory_name to the group by clause?
Logged

Every time god kills an angel, I masturbate.

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #2 on: January 14, 2008, 12:48:03 pm »

Ah so that is what it meant. I was reading the error all wrong. Thanks Martin that worked a charm.
Logged

-- signature --

Pages: [1]   Go Up
Print
 
Jump to: