CODEWITHSUNDEEP
mysql
Thomas Clayson
Thomas Clayson

Reputation: 29925

What's wrong with this mysql query

SELECT v.*, COUNT(a.*) 
FROM vacancies AS v, applications AS a 
WHERE a.vacancy_id = v.id

Basically I need a way of counting the number of applications that correspond to each vacancy.

Applications have a vacancy id. So I want to get the vacancies from the vacancies table and iterate through them outputting the amount of applications that have the same vacancy_id.

Is this the right way to do it? It says I have a problem with the COUNT(a.*) bit.

Any ideas?

Thank you.

Edit

Ok, so I tried the group by thing, and now have this:

SELECT v.*, COUNT(a.candidate_id)
FROM vacancies AS v, applications AS a 
WHERE a.vacancy_id=v.id 
GROUP BY v.title

But it only returns one result, because theres only one application. Is there any way to return EVERYTHING from the vacancies table, even if there are no applications associated with the vacancy?

Upvotes: 0

Views: 76

Answers (4)

MeelStorm
MeelStorm

Reputation: 634

COUNT is a group operation. You need to use GROUP BY at the end.

EDIT Use left join

SELECT v.*, COUNT(a.candidate_id) FROM vacancies AS v LEFT JOIN applications AS a on a.vacancy_id=v.id GROUP BY v.title

Upvotes: 1

Andrea Spadaccini
Andrea Spadaccini

Reputation: 12651

You need a GROUP BY clause. Add something like

   GROUP BY vacancy.somefield

to the query. See COUNT function reference in MySQL

To select all the members of the vacancies table, instead of writing

   WHERE a.vacancy_id = v.id

you should use a LEFT JOIN clause:

   LEFT JOIN applications ON a.vacancy_id = v.id

See the MySQL JOIN documentation.

Upvotes: 1

Aaaaaaaa
Aaaaaaaa

Reputation: 2114

SELECT 
    v.id, COUNT(*)
FROM 
    vacancies AS v, 
    applications AS a 
WHERE 
    a.vacancy_id=v.id
GROUP BY 
    v.id

Upvotes: 0

David
David

Reputation: 5016

You need to GROUP BY something if you are mixing aggregate and names

Upvotes: 0

Related Questions