Query help

sidsel

How can it be my query does not work? It says there is an error near the 'count' function

SELECT , (SELECT COUNT() FROM PICTURES WHERE PIC_TYPE = TYPE_ID) AS PIC_COUNT FROM TYPES ORDER BY TYPE_NAME ASC

dnast

I admit I don't have much experience with subqueries, but I'm guessing that you can't make a mismatch query like that. You'll probably have to have separate queries for the count and general query.

If you're just wanting a count, use the part in the parentheses.

sidsel

It works in MS Access so I thought it would work in MySQL also.
Anyway the tables look like this:

TYPES:

TYPE_ID | TYPE_NAME

21 | Cat0
33 | Cat1
34 | Cat2
43 | Cat3
53 | Cat4

PICTURES:

PIC_TYPE | bla | bla

33 | bla | bla
33 | bla | bla
43 | bla | bla
53 | bla | bla
53 | bla | bla
33 | bla | bla
21 | bla | bla
21 | bla | bla

What I want is to get the number of pictures in PIC_TYPE in the same categori as described in TYPES. I want the TYPE_NAME also...

Lars_Berg

Which version of Mysql are you using?

sidsel

Lars Berg wrote:
Which version of Mysql are you using?

4.0.24

Lars_Berg

That version of Mysql does not support that type of subqueries.

SELECT type_name, COUNT(pic_type) AS PIC_COUNT 
FROM TYPES 
  left join PICTURES on PIC_TYPE = TYPE_ID
group by type_name
ORDER BY TYPE_NAME ASC