Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    32

    Unanswered: Subquery Not in (Select A from B)

    I have a table X, with fields ID(is number) and name. I want to select ID, name from X where ID not in (1,2,3,4), for example.

    I want to put (1,2,3,4) in another table Y with fields A(is number) and B (is string, data = (1,2,3,4))

    How can I make this work:

    select ID, name from X where ID not in (select B from Y where A = 3)


    Above data is only example. The reason I want to do it is that I don't want to put 1,2,3,4 in seperate records.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot do it that way. They have to be separate records.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    At least, there are two issues.
    1) You are comparing list of values with a column.
    2) You are comparing character string with a number.

    Anyhow, this may be an solution:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
    /* Test data */
     X(id, name) AS (VALUES
     ( 1, 'Onnes, Heike Kamerlingh') 
    ,( 2, '')
    ,( 4, 'Fourier, J. B. Joseph')
    ,( 5, 'von Neumann, John')
    ,( 6, 'Silberberg, Robert')
    ,(10, 'Tensor')
    )
    ,Y(a, b) AS (VALUES
     (1, '2,4,6,8')
    ,(3, '1,2,3,4')
    ,(9, '3,6,7,10')
    )
    /* End of Test data */
    
    SELECT x.id, x.name
      FROM X
      JOIN Y
       ON  y.a = 3
       AND LOCATE(RTRIM(CHAR(id))||',', y.b||',') = 0
    ;
    ------------------------------------------------------------------------------
    
    ID          NAME                   
    ----------- -----------------------
              5 von Neumann, John      
              6 Silberberg, Robert     
             10 Tensor                 
    
      3 record(s) selected.

  4. #4
    Join Date
    Sep 2003
    Posts
    32
    Thanks for the solution. After second thought, I am going to put the data on seperate records since my real data is more than that. Thanks for the reply anyway.

Posting Permissions

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