Select distinct field A if none of columb B =x - may inolve GROUP function

[tendrousbeastie]

Hi all,

 

Sorry for the stupid title, but I'm not sure how to summarise my question any more succinctly.

 

I am updating a database that stores linguistic translations, so it is set up to have a tabl that has a row per translation job, and then a separate table that stores a the separate languages for each job (as a particular file may go into several languages).

 

The table of the individual languages is where I am stuck. The three relevant fields for this question are:

 

ID            =                A numeric ID for the row (this is not referenced anywhere else at all, it is purely there to act as a primary ket)

 

JOB_ID    =              stores the INT value of the primary key of the main jobs table. This is how I can associate the various languages as being part of one job (i.e. they will all share the JOB_ID value)

 

WORD_COUNT  =    An INT, storing the number of words in the translated file.

 

 

 

What I want is a SELECT query that will return a DISTINCT set of JOB_IDs, where each returned JOB_ID has all of its WORD_COUNTS = NULL (or '' or 0,or whatever, but where they all equal some value).

 

A basic "SELECT job_id FROM table WHERE word_counr IS NULL" obviously return the job_id for each set where any word_count is null. How do I do it to return only those where all are NULL.

 

 

Ta for any help...

[Gamic]

You'd want something like this, I'd imagine:

select distinct(job_id) as job_id, id, word_count from job
where word_count is null
and job_id not in
  (select distinct(job_id) from job where word_count>0)
group by job_id;

[fenway]

There is an ALL subquery option...

[tendrousbeastie]

Thanks for the help chaps.

 

Gamic, I'll try your query and see if I can get it working. Hopefully from this I can work out how the group option works, as I've not used it before.

 

 

Fenway, can you tell me more about what the ALL subquery is and does?

[fenway]

Fenway, can you tell me more about what the ALL subquery is and does?

Had you read this?