difficulty with a query

[mozart]

I do apologise if i am being a pain but i am having some difficulty with a query. (I am a novice)

I have two tables ‘WORK’ and ‘WORKHIST’


WORK’
----------

JOB WORKER STATUS
1222 AB A
1223 AB B
1224 CD B
1225 AB B
1226 CD A

WORKHIST
----------------
JOB WORKER DESC
1222 AB NULL
1222 AB NULL
1222 AB SOME TEXT
1224 CD NULL
1224 CD NULL
1225 AB NULL
1225 AB NULL

I wish to find all JOBS where STATU=’A’ and DESC IS NULL

Problem is I can’t get a query where I don’t get multiple rows as I do the join on
WORK.WORKER=WORHIST.WORKER

I want a single unique row for each JOB which meets the described condition.

Help appreciated

Mozart

[Srinivas_Sharma]

hi,

select w.job,w.worker,w.status,wh.descr from work w, workhist wh where w.job=wh.job and w.status='A' and wh.descr is null;

if u issue the above query u still will get 2 records bcos ur conditions r being satisfied....

the output will be

JOB WO S DESCR
---------- -- - ----------
1222 AB A
1222 AB A


Sri

[mozart]

I possibly did not explain myself correctly in that when i said i want a unique job i meant '1222' to appear ONCE and NOT twice.

I already know that JOB 1222 failed and if this job had say 1000 history entries then I would get 1000 X '1222' rows.

In actual fact lets say the status changed 1000 times causing a new history row to be created, the query would not technically give me the each wf.job (or job history) created when status was set to 'A' and the history description was left NULL.

There is the possibiltiy that the status wash changed to B 20 times before finnally back to 'A' but this does not mean that there were 20 histories created for work.status='A'



This is confusing stuff for a newbie like me

[DaPi]

Originally posted by mozart
This is confusing stuff for a newbie like me

It's confusing for everybody! I don't really understand your requirements.

Some observations:

1) You seem to have a denormalised data model - the relationship JOB-to-WORKER is represented twice. This needs some explaining.

2) Your history table should have some kind of sequence number or date/time stamp to allow you to see in what order things happened.

3) If you replace SELECT by SELECT DISTINCT in Sri's query, you might get what you want.

[mozart]

Ok lets then say i have a date stamp for each row in my history table.

How could i say get only the most recent workhistory.job where workhistory.job=1222? using the tables datestamp
EG

I get

WORKHIST.JOB |WORKHIST.WORKER |WORKHIST.STATUS|WORKHIST.DATESTAMP
-------------

1222 AB A 12/03/2004
1222 AB A 01/04/2004 <----


so i only get the most recent workhist.job based on the workhist.datestamp


Remember i could end up getting 1000 records with the same job # but i only want the most recent one.

[Srinivas_Sharma]

Well if u add a datestamp column, how will u handle 2 jobs on the same day but at different times?

how will u pick the recent one?

Dont u think u shuld store the time as well.

if its one job only and u have a datestamp column, this might help u

select w.job,w.worker,w.status,wh.descr,wh.datestamp from work w, workhist wh where
w.job=wh.job and w.status='A' and wh.descr is null and datestamp = (select max(datestamp)
from workhist where workhist.job=w.job);

Sri

[mozart]

What I think the problem here is that its grabbing the maximum date of all the records in the history table.

It should grab only the maximum date stamp record for each job number in the history table

So if

Job Date

1222 01/04/2004
1222 31/03/2004

1233 28/03/2004
1233 15/03/2004
1233 11/03/2004

Then return

Job Date

1222 01/04/2004
1233 28/03/2004


AND NOT

JOB Date

1222 01/04/2004

Thanks for your time

[Srinivas_Sharma]

It will get max date for the records which have a corresponding job number and where w.status='A' and wh.descr is null

Hope thats what u wanted.

Assuming u have
1233 A NULL

in work table

and in work_hist table u have corresponding

1233 AB 28/03/2004
1233 AB 15/03/2004
1233 AB 11/03/2004

my query wiill retrive what u want..it will get the max date for the jobs.

so the output will be

JOB WO S DESCR DATESTAMP
---------- -- - ---------- ---------
1222 AB A 01-APR-04
1233 AB A 28-MAR-04


It gets whatever conditions u have specified

Srini

[mozart]

Thankyou.

You are a champion

[slimdave]

You might like to look at using analytic functions for finding the maximum date per jobid -- there are a lot of examples in the forum.