[SOLVED] select query with join and group by clause.

[zohab]

Hi,

 

I have 3 tables

 

1.jobs 2.candidate_information 3.job_candidate_information_relationship

 

 

jobs =>

 

job_id|job_title

1|job1

2|job2

3|job3

4|job4

5|job5

 

candidate_information=>

 

candidate_information_id|candidate_name

1|candidate1

2|candidate2

3|candidate3

4|candidate4

5|candidate5

 

job_candidate_information_relationship=>

 

id|job_id|candidate_information_id

1|1|1

2|1|2

3|1|4

4|1|5

5|2|2

6|2|4

7|3|1

8|3|5

9|3|2

9|4|3

9|5|5

 

 

I want to select all jobs by candidates.

 

Output=>

 

Jobs |  Candidates

job1 |  3

job2 |  2

job3 |  3

job4 |  1

job5 |  1

 

Select job.job_title and count of candidates for job ??

 

 

 

 

My mysql version is 5.0

[kickstart]

Hi

 

I would try something like this:-

 

SELECT a.job_title, b.candCount
FROM jobs a
INNER JOIN (SELECT job_id, COUNT(candidate_information_id) AS candCount
FROM job_candidate_information_relationship
GROUP BY job_id) b
ON a.job_id = b.job_id

 

You could possibly get away with simplifying it into a simple JOIN and GROUP BY the job_title, but that would give dodgy results if there were 2 jobs with the same title and would also make it more difficult to extract further columns from jobs.

 

All the best

 

Keith

[cags]

Off the top of my head something like this should work...

 

SELECT j.job_title, COUNT(jcir.id) FROM jobs j JOIN 
job_candidate_information_relationship jcir ON jcir.job_id=j.job_id
JOIN candidate_information ci ON ci.candidate_information_id=jcir.candidate_information_id
GROUP BY job_id

 

Edit: kickstart beat me to it, their method could well be better, I haven't checked. Hell mine not work

[kickstart]

Hi

 

Yours might work (if you put job_id into the list of fields you are returning) but is reliant on a "feature" of MySQL. Most flavours of SQL violently object to having non aggregate fields selected that are not in the GROUP BY list. MySQL doesn't worry too much about this (although the values might be a bit dodgy if not directly related to a GROUP BY field), but it is something that I try not to rely on.

 

All the best

 

Keith

[zohab]

Great Help by cags and Keith

 

Thanks guys 

[cags]

Since the topic is marked as solved. Without trying to sound like a complete idiot, whats the definition/difference between non-aggregate and aggregate fields?

[kickstart]

Hi

 

By aggregate fields I mean ones such as COUNT, MAX, MIN, etc. Basically ones that are the result of a "calculation" on a group of fields.

 

To explain the issue I was pointing out, say you had a table of:-

 

FirstName, Surname

John, Smith

Jo, Smith

Fred, Bloggs

Bilbo, Baggins

 

If you did:-

SELECT Surname, COUNT(*) FROM SomeTable GROUP BY Surname

 

Then you would get:-

Smith, 2

Bloggs, 1

Baggins, 1

 

However if you grabbed the surname as well without using it in the GROUP BY:-

 

SELECT Surname, FirstName, COUNT(*) FROM SomeTable GROUP BY Surname

 

Then you might get:-

Smith, John, 2

Bloggs, Fred, 1

Baggins, Bilbo, 1

 

or you might get:-

Smith, Jo, 2

Bloggs, Fred, 1

Baggins, Bilbo, 1

 

However most flavours of SQL would error. MySQL will attempt to bring it back but the value of the non GROUP BY / non aggregate field is not reliable.

 

All the best

 

Keith

[cags]

Ahh I see, that makes sense. Thanks for the exlanation.