Subquery Not in (Select A from B)

newer · #1 (**permalink**) 04-28-09, 15:51

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.

ARWinner · #2 (**permalink**) 04-28-09, 16:04

You cannot do it that way. They have to be separate records.

Andy

tonkuma · #3 (**permalink**) 04-28-09, 17:03

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.

newer · #4 (**permalink**) 04-29-09, 08:29

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech