Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: How to write a query that returns every value which is in a set but not in a table?

    I need help with writing a query for this poblem: I have a set of values eg. '1,2,3,4,5' and I want to write a query that will give me every value from my set which is *not* in a specific row in a specific table. eg. if the column contains three rows '1,2,3' I want the query to return 4,5.

    I though about the "select * from table where value not in ('1,2,3,4,5')" but it doesnt work (in this case it will return 1,2,3 and not 4,5...)

    Is there an easy way that will do what I want?
    Last edited by tzvikaper; 09-28-12 at 19:55.

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Ok, here are a couple of examples, but obviously the search_for_string and the value in the column always have to be in the same order:
    Code:
    select '1,2,3' as a,
           regexp_replace(
           decode( substr( '1,2,3', 1, 1 ), '1', '1,' ) ||
           decode( substr( '1,2,3', 3, 1 ), '2', '2,' ) ||
           decode( substr( '1,2,3', 5, 1 ), '3', '3,' ) ||
           decode( substr( '1,2,3', 7, 1 ), '4', '4,' ) ||
           decode( substr( '1,2,3', 9, 1 ), '5', '5,' ), ',$', '' ) as result
     from  dual
     
    A      RESULT
    1,2,3  1,2,3
    
    select '4,5' as a,
           regexp_replace(
           decode( substr( '1,2,3', 1, 1 ), '1', '1,' ) ||
           decode( substr( '1,2,3', 3, 1 ), '2', '2,' ) ||
           decode( substr( '1,2,3', 5, 1 ), '3', '3,' ) ||
           decode( substr( '1,2,3', 7, 1 ), '4', '4,' ) ||
           decode( substr( '1,2,3', 9, 1 ), '5', '5,' ), ',$', '' ) as result
     from  dual
    
    A    RESULT
    4,5  1,2,3
    But I've got a feeling this is not really what you are trying to accomplish.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have a set of values eg. '1,2,3,4,5'
    Oracle & I disagree with the statement above.
    You have a single string that is 7 characters long; which contains 4 commas.
    Why do folks insist on trying to store multiple values in a single column?
    A single column should only ever contain a single value or NULL.
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I agree with anacedet: you should really re-think your database design. This kind of storing multiple values in a column is begging for problems in the long run
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Sep 2012
    Posts
    2
    I think I didnt make myself clear enough - every element in the set is stored in a single row. I just want to find what are the colums in the table which dont appear in the set I have. for example, if I have got a column named ID I want to get all the rows where their ID appears in what I give in the query (and I gave example above) but not in the table's rows... like the "not in" operation but returning the opposite-what is in the parameter and not in the table.

    I hope that now you will better understand what I ment for.
    Last edited by tzvikaper; 09-28-12 at 19:57.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We speak SQL.
    Do you speak SQL?
    If so post CREATE TABLE for needed tables & INSERT to populate table(s) with sample data.
    post expected & desired results from sample data & explain how & why this results set is produced
    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.

  7. #7
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Show us the table description:
    Code:
    select atc.table_name, atc.column_name, atc.data_type as "type", atc.data_length as "length",
           atc.data_precision as "precision", atc.data_scale as "scale", atc.nullable, acc.comments
     from  sys.all_tab_columns atc left outer join sys.all_col_comments acc on atc.owner        =  acc.owner
                                                                           and atc.table_name   =  acc.table_name
                                                                           and atc.column_name  =  acc.column_name
    where  atc.owner       =  'SCHEMA_NAME'
     and   atc.table_name  =  'TABLE_NAME'
    order by atc.table_name, atc.column_id

Posting Permissions

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