help with select statement 2

[sql99]

Hello everyone,

Sorry to keep asking questions but I need help with a select statement. I have the following select statement:

SELECT distinct(pers.pers_id),sta.per_status, add.per_address, job.job_category
FROM person pers, status sta, address add, jobs job
WHERE sta.per_status in ('Full', 'Part')
AND job.job_category in ('RECEPTIONIST', 'CLERK', 'CASHIER', 'AGENT')
AND job.status <> 'ACTIVE'
AND add.state = 'GEORGIA'
AND pers.pers_id = sta.pers_id
AND sta.pers_id = add.pers_id
AND add.pers_id = job.pers_id;

I would like the query to pull all employees that are 'Full-time' and 'Part-time' with job_categories being either receptionist, clerk, cashier, or agent and for ALL of those existing job categories to not be 'ACTIVE'. However, the query above is pulling data back even if one of the job_categories is not 'ACTIVE'. For example, if person 123 belongs in both the RECEPTIONIST and CLERK categories and the CLERK category is not 'ACTIVE' but the RECEPTIONIST category is 'ACTIVE', it still pulls the record for CLERK. I would like it to pull data only if RECEPTIONIST and CLERK are BOTH not 'ACTIVE'

I hope I didn't confuse anyone. I appreciate any help I can get on this.

Thanks again,
sql99

 

[SantaMufasa]

SQL,

I didn't follow completely, but I will tell you about the rows that are appearing: Each row MUST

a) be either "Full" or "Part" status
b) not = 'ACTIVE' for job status
c) be from 'GEORGIA'
d) have a record in person, status, address, and jobs, all sharing the same "pers_id".

Is this what you are getting and what you wanted? If not, tell us what you wanted instead.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 03:23 (10Dec04) UTC (aka "GMT" and "Zulu"),
@ 20:23 (09Dec04) Mountain Time

 

[SantaMufasa]

Sorry, SQL, I forgot to add that any row that displays also must have a job_category of either 'RECEPTIONIST', 'CLERK', 'CASHIER', or 'AGENT'.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 03:41 (10Dec04) UTC (aka "GMT" and "Zulu"),
@ 20:41 (09Dec04) Mountain Time

 

[sql99]

Hi Dave,

Yes, that's what it should do, however I only want it to return data for people who belong in those categories but have to be not 'active'. In other words, if person abc is both CLERK and AGENT, but only CLERK is not active, then it shouldn't pull any data unless they're both active. So I need to add a condition where all the categories they're in are not active, otherwise don't display anything even if one or a few of them are not active. Please let me know if I confused you some more.

Here's another example which I hope will make things clearer:

pers_id per_status pers_address job_category
abc ACTIVE 1234 King St CLERK
bff INACTIVE 1214 Burke Rd RECEPTIONIST

I would like this NOT to return in the query since there is one existing row (abc) that is active.

pers_id per_status pers_address job_category
abc INACTIVE 1234 King St CLERK
bff INACTIVE 1214 Burke Rd RECEPTIONIST

I would like this to pull in the query since both existing rows that meet all the conditions.

Thanks,
sql99

 

[kskid]

Try this. Remove any of the statements in the Where exists that do not apply (i.e. other than 'ACTIVE')

SELECT distinct(pers.pers_id),sta.per_status, add.per_address, job.job_category
FROM person pers, status sta, address add, jobs job
WHERE NOT EXISTS
(
SELECT *
FROM jobs job1
WHERE job1.status = 'ACTIVE'
AND sta.per_status in ('Full', 'Part')
AND job.job_category in ('RECEPTIONIST', 'CLERK', 'CASHIER', 'AGENT')
AND add.state = 'GEORGIA'
AND job1.pers_id = jobs.pers_id
)
AND sta.per_status in ('Full', 'Part')
AND job.job_category in ('RECEPTIONIST', 'CLERK', 'CASHIER', 'AGENT')
AND job.status <> 'ACTIVE'
AND add.state = 'GEORGIA'
AND pers.pers_id = sta.pers_id
AND sta.pers_id = add.pers_id
AND add.pers_id = job.pers_id;