Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    8

    Unanswered: [Solved] Check each of multiple result records for specific creiteria

    Apologies if this is vague.. I only have to Oracle SQL Developer 3.0.0.4.

    The table i'm querying looks something like this
    Code:
    GBHINSTALL	GBHREQTYPE	GBHREQDATE	GBHSTATUS
    10002017		GV	25/06/2012	0
    10002017		FR	26/06/2012	3
    10002079		FR	21/06/2012	3
    10002079		S	26/06/2012	3
    10008666		GN	18/06/2012	3
    10017288		E	21/06/2012	10
    What i'm wanting to do is output a list of those installs that do not have an 'FR' header

    So in the above example, the only installs i want are 10008666 & 10017288.

    I've tried sql like:
    Code:
    select hdr.gbhinstall, hdr.gbhreqdate, hdr.gbhreqtype
    from gentrack.gbheader hdr
    where hd.gbhreqtype <> 'FR'
    This still selects all 4 installs, with the associated headers of the non-FR types

    Any help would be much appreciated.
    Last edited by deamo; 06-21-12 at 16:05. Reason: solved.

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try this:
    Code:
    with gbh_type as(
      select distinct gbhinstall
       from  gentrack.gbheader
      where  gbhreqtype  =  'FR' )
    select gbh.*
     from  gentrack.gbheader gbh
    where  not exists ( select gbhinstall
                         from  gbh_type
                        where  gbhinstall  =  gbh.gbhinstall )

  3. #3
    Join Date
    Jun 2012
    Posts
    8
    Brilliant, thanks spacebar.

    Is there some trick to grouping the result of your query?

    i've tried using distinct as well as group by in second select, both to no avail
    Code:
    with gbh_type as(
      select distinct gbhinstall
       from  gentrack.gbheader
      where  gbhreqtype  =  'FR' )
    select distinct gbh.*
     from  gentrack.gbheader gbh
    where  not exists ( select gbhinstall
                         from  gbh_type
                        where  gbhinstall  =  gbh.gbhinstall )

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT gbhinstall, 
           gbhreqtype, 
           gbhreqdate,
           gbhstatus 
    FROM   gbh 
    WHERE  gbhinstall IN (SELECT gbhinstall 
                          FROM   gbh 
                          MINUS 
                          SELECT gbhinstall 
                          FROM   gbh 
                          WHERE  gbhreqtype = 'FR') 
    
    /
    Last edited by anacedent; 06-21-12 at 00:50.
    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.

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Nope no tricks, for example to get a count by gbhinstall and gbhreqtype:
    Code:
    with gbh_type as(
      select distinct gbhinstall
       from  gentrack.gbheader
      where  gbhreqtype  =  'FR' )
    select gbh.gbhinstall, gbh.gbhreqtype, count(*)
     from  gentrack.gbheader gbh
    where  not exists ( select gbhinstall
                         from  gbh_type
                        where  gbhinstall  =  gbh.gbhinstall )
    group by gbh.gbhinstall, gbh.gbhreqtype

  6. #6
    Join Date
    Jun 2012
    Posts
    8
    Thanks spacebar.

    I now know what i was doing wrong.. don't know why i couldn't see it earlier.

Posting Permissions

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