Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Question Unanswered: Query involving join

    I am having some problems with a query.

    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 wjocj meets the described condition.

    Help appreciated

  2. #2
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20
    if you want find out every job with almost a worker with status "A" and without any desc you can try this:

    select
    WORK.JOB
    from work,WORKHIST
    where WORK.JOB=WORHIST.JOB
    group by WORK.JOB
    having
    max(WORKHIST.DESC) is null and
    min(WORK.STATUS)='A';

    Please note that I used the fact that 'A'<'B'...
    Last edited by zeus77; 04-01-04 at 07:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •