Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: Query Help (SQL Developer)

    Hello all, my first post here so hopefully someone can help me out

    My background is in Chemical Engineering, but I have inherited access to our DB that handles all our sample data and need help getting a query to work. In one table, we have sets of data set up as follows:

    SELECT d.sample, d.test, d.result
    FROM data d
    WHERE d.test IN ('Lead','Chromium')
    ORDER BY d.sample, d.test

    SAMPLE TEST RESULT
    C001 Chromium 0.01
    C001 Lead 0.01
    C002 Chromium 0.18
    C002 Lead 0.05
    etc.

    What I want to be able to do is see both the Lead and Chromium results, but only for samples where the Chromium results are greater than 0.01. Any help would be appreciated. Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT d.sample, d.test, d.result
    FROM data d
    WHERE d.test = 'Lead'
    (OR d.test = 'Chromium'
    AND d.results >= 0.01)
    ORDER BY d.sample, d.test
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Posts
    3
    Thank you for the response.

    Unfortunately, a query like that returns all Lead results, and then also only the Chromium results > 0.01, i.e.:

    SAMPLE TEST RESULT
    C001 Lead 0.01
    C002 Chromium 0.18
    C002 Lead 0.05

    In that example, I want to run a query and ONLY see the Lead and Chromium results for sample C002 (but for all samples that meet that criteria).

    Another way of explaining it: If a sample has a Chromium result > 0.01, then display both the Lead and Chromium results, else display nothing.
    Last edited by Striken; 10-22-10 at 11:50.

  4. #4
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Try this:
    SELECT d.sample, d.test, d.result
    FROM data d
    WHERE exists (select d.sample from data where test = 'Chromium' AND result >= 0.01)
    ORDER BY d.sample, d.test
    Thanks and Regards,

    Praveen Pulikunnu

  5. #5
    Join Date
    Oct 2010
    Posts
    3
    Thank you. I was very hopeful that that would work, but still no luck. That query returns all results, as if the subquery section is being ignored (or is being counted as exists for all samples).

    I verified that the subquery itself is working correctly, returning sample names for only those samples I'm interested in. Knowing that, I also tried:

    SELECT d.sample, d.test, d.result
    FROM data d
    WHERE d.sample = (select d.sample from data where test = 'Chromium' AND result >= 0.01)
    ORDER BY d.sample, d.test

    But I get the error message:

    ORA-01427: single-row subquery returns more than one row
    01427. 00000 - "single-row subquery returns more than one row"

    I know that the subquery is returning multiple rows, one row for each sample name, but apparently there's a problem with trying to do what I did.

    EDIT:

    This query seems to do what I wanted:

    SELECT d.sample, d.test, d.result
    FROM data d
    WHERE d.sample in (select d.sample from data where test = 'Chromium' AND result >= 0.01)
    ORDER BY d.sample, d.test
    Last edited by Striken; 11-02-10 at 15:21. Reason: Solved

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Baby steps.

    This is what we have:
    Code:
    SQL> select * from test;
    
    SAMP TEST                 RESULT
    ---- -------------------- ------
    C001 Chromium               0.01
    C001 Lead                   0.01
    C002 Chromium               0.18
    C002 Lead                   0.05
    
    SQL>
    What conditions does the final result set have to fulfill? Chromium's result must be greater than 0.01. Obviously, that's C002:
    Code:
    SQL> select sample
      2  from test
      3  where test = 'Chromium'
      4    and result > 0.01;
    
    SAMP
    ----
    C002
    
    SQL>
    So, let's use it:
    Code:
    SQL> select t.sample, t.test, t.result
      2  from test t
      3  where t.sample in (select sample
      4                     from test
      5                     where test = 'Chromium'
      6                       and result > 0.01
      7                    )
      8  order by t.sample, t.test;
    
    SAMP TEST                 RESULT
    ---- -------------------- ------
    C002 Chromium               0.18
    C002 Lead                   0.05
    
    SQL>
    Satisfied?

    [EDIT] So you solved it while I was creating an answer. Good for you!

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Sample data for below SQL:
    Code:
    SQL> select * from test;
    
    SAMP TEST                 RESULT
    ---- -------------------- ------
    C001 Chromium               0.01
    C001 Lead                   0.01
    C002 Chromium               0.18
    C002 Lead                   0.05
    C003 Lead                   0.05
    C004 Chromium               0.11
    Do you mean that you want the results of any sample that has both a lead and chromium test, and has a chromium level greater than .01? Meaning if you have a chromium level greater than .01, but no lead test, then it is ignored?

    If so, you would want something like:
    Code:
    Select a.samp, a.test, a.result
        from test a
    where exists (select 1 from test b
                       where a.samp = b.samp
                           and a.test = 'chromium'
                           and a.result > .01
                           and exists (select 1 from test c
                                          where b.samp = c.samp
                                              and c.test = 'lead'))
    result:
    Code:
    SQL> select * from test;
    
    SAMP TEST                 RESULT
    ---- -------------------- ------
    C002 Chromium               0.18
    C002 Lead                   0.05
    Or do you mean you want any samples that have lead or a chromium level greater than .01?
    If so, maybe something like:
    Code:
    Select a.samp, a.test, a.result
        from test a
    where a.test in ('chromium','lead')
       and ((a.test = 'chromium'
           and a.result > .01)
          or a.test = 'lead'
    result:
    Code:
    SQL> select * from test;
    
    SAMP TEST                 RESULT
    ---- -------------------- ------
    C001 Lead                   0.01
    C002 Chromium               0.18
    C002 Lead                   0.05
    C003 Lead                   0.05
    C004 Chromium               0.11
    Dave Nance

Posting Permissions

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