[RESOLVED] Job count including no jobs

David_James

I have this sql:

select user.username, count(*) as jobs from user, job where user.user = job.user group by user.user;

Which returns:

username: edwina
jobs: 1

username: martina
jobs: 2

username: company
jobs: 1

However, I have two other users in the user table who have no jobs in the job table. I want those to return:

username: otheruser
jobs: 0 (or NULL would be fine)

Is this possible in one sql statement??

konsu

hello, you can use left join to join user table to job table. the code you posted uses inner join, so you do not get users with no jobs.

FraXTC

I can't help you david but I would like to add that I myself need the exact same information. I tried using a WHERE statement, like

$query = "SELECT name, COUNT(*) AS count FROM table WHERE count > -1 GROUP BY name ORDER BY name";
include('query.php');

This way I hoped that MySQL would understand that zero is also an answer but it says that count is an unknown collu........Wowowowow... Wait a second.. I know why it fails: a count result of 0 means that there is no entry that holds that name!

For example:

There are 4 persons that work in your office: Peter, Jack, Mike and Patty

At the moment only Peter, Jack and Mike have work. Your "work_table" would look like:
[Name] ------- [Job]
Peter ------- Fix the Kitchen
Peter ------- Connect the tubes
Jack -------- Seal the windows
Mike -------- Fix the doors

A count command would give:
Peter : 2
Jack : 1
Mike : 1

Now why doesn't patty show up? Coz there isn't anyone named Patty in the work_table, so how on earth is MySQL supposed to know that there's also a Patty working in your company?

There are two tricks: one of which I DO know how to use it, and one of which I DON'T.

The trick I know is to give every employee a fake entry which never gets deleted, And THEN use the count funtion, but substract one:

$query = "SELECT name, COUNT(*) - 1 AS count FROM table GROUP BY name ORDER BY name";

This way the name patty is also mentioned in the work_table and will thus show up as an answer. But since patty has only 1 entry (the fake one) the result of the query will be 1-1 = 0 !

The second trick (in my opinion a better solution) is to somehow "force" MySQL to query for a person named Patty. The answer would logically be 0, since Patty is unknown as a name. I've tried this way, but without any luck so far. If anyone else can help?....

David_James

Thanks FraXTC for your input. Interesting that we have the identical problem. I'm really going to have to learn about this stuff.

konsu wrote:
hello, you can use left join to join user table to job table. the code you posted uses inner join, so you do not get users with no jobs.

I'm not quite sure how to do that. I went to mysql join syntax and got thoroughly confused.

konsu

David James wrote:
I'm not quite sure how to do that. I went to mysql join syntax and got thoroughly confused.

something like

select * from user left join job on user.user=job.user

this will result in a table that has all the rows from the user table with the corresponding rows from job table appended. if there is no corresponding row in job table, then NULL fields will be appended (unlike in inner join where the user row will not be output at all). if there are more than one row in job table that have the same user.user, the same row from user table will be output with each different job row appended. i hope this makes sense.

David_James

Well it may not make sense to me right now (that will change!), but it does work. I adjusted your sql to accomplish what I and FraXTC were trying to accomplish:

select username, count(job) from user 
     left join job on user.user = job.user group by user.user;

and sure enough, I get a list of all users and the number of jobs each one has!

username: admin
count(job): 0

username: edwina
count(job): 1

username: martina
count(job): 2

username: company
count(job): 1

username: someone
count(job): 0

Thank you so much konsu! 🙂

FraXTC

EUREKA! I just found a way to "fake it" in mysql!

$query = "SELECT 'Patty' as name, COUNT(*) as nr_of_jobs FROM work_table WHERE name = 'Patty' ";
//Execute query

This will give as a response:

Name -------- nr_of_jobs
Patty --------- 0

++ edit ++
lol ok, you win konsu :p... But I don't need to combine two tables, since I always have just 2 options. Anyway: very informative, I'll check it out anyhow, because maybe I'll need it some time from now 🙂

Drakla

FraXTC, you have to use HAVING when using a group by column has part of a condition, your faffing doesn't need to happen.

David_James

I have a third table which is also RELATED to the previous two. Therefore:

user table:
primary key: user

job table:
primary key: job
foreign key: user

forms table:
primary key: forms
foreign key: job
foreign key: user

Thus, to select all users, jobs and forms the following code worked:

select user.user, job.job, forms.forms from user 
	left join job on user.user = job.user 
	left join forms on job.job = forms.job and job.user = forms.user

I'm learning...

konsu

forms table:
primary key: forms
foreign key: job
foreign key: user

note that a job ID uniquely identifies its user through the foreign key. so it seems that your forms table does not require either "job" or "user" field.

David_James

Perhaps I don't understand what a foreign key is...

One thing I DON'T have the advantage of is using the InnoDB tables, thus related tables require a little more work. Essentially what I am doing is creating a multi-column index wherein the table_name_col is the auto_increment primary field and the ascendent tables (related) have their own columns. I guess when it comes down to it, there is no absolutely unique column in any table (except for the top one "user"). What is unique is the COMBINATION of the related columns. Thus user: 1, job: 1, forms: 1 and user: 2, job: 1, forms: 1 reference two completely unique forms. As you can see each table (according to this scheme) MUST have all ascendent keys. This seemed to me to be a logical way of doing it... :bemused:

konsu

i was just saying that the table below has enough information to find the user:

forms table: 
primary key: forms 
foreign key: job 
[code=php]
you do not need the user field that you have now. because jobs are already associated with users. so your example in bold in your last post does not make sense to me. you cannot have job 1 for both user 1 and user 2.

David_James

show create table user

CREATE TABLE user (
  user int(10) unsigned NOT NULL auto_increment,
	.. other columns ..
  PRIMARY KEY  (user)
) TYPE=MyISAM

show create table job

CREATE TABLE job (
  user mediumint(8) unsigned NOT NULL default '0',
  job int(10) unsigned NOT NULL auto_increment,
	.. other columns ..
  PRIMARY KEY  (user,job)
) TYPE=MyISAM

show create table forms

CREATE TABLE forms (
  user mediumint(8) unsigned NOT NULL default '0',
  job mediumint(8) unsigned NOT NULL default '0',
  forms int(10) unsigned NOT NULL auto_increment,
	.. other columns ..
  PRIMARY KEY  (user,job,forms)
) TYPE=MyISAM

I thought I would show you the actual tables here to help explain. Again, the main point is that UNIQUENESS is based on the COMBINATION of the primary keys. Thus in the job table it is possible to have both:

user: 1
job: 1

AND

user: 2
job: 1

If you have time (or anyone does) I would be interested to know what they thought of this approach or what other approaches there may be. 🙂

konsu

i see. so the job table's primary key is not just job field, but a pair of fields (user,job). i do not know how better this is than the "standard" approach. that is, the job field is the primary key and the user field is a foreign key pointing to the primary key in the user table. this way, every row in job table is associated with the corresponding row in the user table through the foreign key. i never myself used your approach.