Results 1 to 7 of 7

Thread: SQL select statement (need help)

  1. #1
    Join Date
    Mar 2003
    Posts
    9

    SQL select statement (need help)

    I have the following information in a table:

    OBJECT PATHCODE
    --------- ------------


    B75A00094 DV7333
    B75A00094 PY7333
    B75A00095 DV7333
    B75A00095 PY7333
    B75A00096 DV7333
    B75A00096 PY7333
    B75A00096 PD7333
    B75A00097 PD7333
    B75A00097 PY7333
    B75A00097 DV7333

    I need to form a select statment that would only give me the "OBJECT" that do not have the "PATHCODE" value of "PD7333". In this example, I would only want B75A00094 and B75A00095 returned as the results. Any suggestions would be greatly appreciated.
    Last edited by JA0822; 03-08-2003 at 04:50 PM.

  2. #2
    Join Date
    Mar 2003
    Posts
    9
    The results in this example would contain 2 rows of data.
    Last edited by JA0822; 03-08-2003 at 09:38 PM.

  3. #3
    Join Date
    Feb 2003
    Posts
    102

    Left, Right

    If you are using JET (Access) or SQL Server (not sure about the rest and you didn't say...)

    SELECT Left(tblPathCode.PathCode,9)
    FROM tblPathCode
    WHERE (Right(tblPathCode.PathCode,6) = 'PD7333')

    will do the trick.

    HTH,

    Peter

  4. #4
    Join Date
    Mar 2003
    Location
    CA,USA
    Posts
    18
    Hi Ja0822

    Technically this would be the query

    SELECT DISTINCT object
    FROM table
    WHERE pathcode NOT IN ('PD7333')
    /

    DISTINCT -- to supress duplicate display
    NOT IN -- The SQL operator to exclude your data value

    thanks

  5. #5
    Join Date
    Mar 2003
    Location
    CA,USA
    Posts
    18
    By the way, forgot to mention that was with respect to ORACLE.

  6. #6
    Join Date
    Mar 2003
    Location
    CA,USA
    Posts
    18

    CORRECTION

    Oops...I missed your point earlier.

    This is the one:
    SELECT DISTINCT object
    FROM table
    WHERE pathcode NOT IN ('PD7333')
    AND object NOT IN (SELECT object
    FROM test
    WHERE pathcode='PD7333')
    /

  7. #7
    Join Date
    Mar 2003
    Posts
    9

    Works Great!

    Thanks so much. I realize now what I was doing wrong earlier. Thanks again!

Posting Permissions

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