Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2012
    Posts
    5

    Unanswered: Issue in verifying two conditions!!

    Hello All,

    I am new to database and I need help for the following issue.

    I have a table with column name code and I want to check two things for this column.
    1.Check code1 in with few values.
    2.check code1 not in few values

    and I am joining this table with another table so that I can display few columns from these 2 tables.

    And code is primary key in table1 and code1 is primary key in second table.

    Thanks in advance for your help

    Sachin

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    in with few values
    Code:
    select * from tableA a, tableB b
    where a.code1 = b.code1
      and a.code1 in ('VALUE1', 'VALUE2', 'VALUE3');
    Not in few values
    Code:
    select * from tableA a, tableB b
    where a.code1 = b.code1
      and a.code1 not in ('VALUE1', 'VALUE2', 'VALUE3');

  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 table with column name code and I want to check two things for this column.
    >1.Check code1 in with few values.
    >2.check code1 not in few values

    please quantify "few"
    what is "few" for me may be many for you.

    How will you or I decide when correct solution is posted?
    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
    Jul 2012
    Posts
    5
    I want both conditions in one single sql statement

    Thanks
    Sachin.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    both conditions in one single statement
    Code:
    select * from tableA a, tableB b
    where a.code1 = b.code1
      and a.code1 in ('VALUE1', 'VALUE2', 'VALUE3')
      and a.code1 not in ('VALUE4', 'VALUE5', 'VALUE6');

  6. #6
    Join Date
    Jul 2012
    Posts
    5
    Thank you and it works for me.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Makes no sense though. The not in is never used. the in clause will restrict what you see and the not in except if they have common values will be ignored.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2012
    Posts
    5
    After seriously going through the data I see what you are saying is makes sense.
    Can you help me on this?

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I was making a comment on the example shown. However the optimizer will take care of using what it needs and the format used will work fine. If the values in "not in" are never going to be in the "in" list, then simply leave it out.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jul 2012
    Posts
    5
    In my case there are some common values.

Posting Permissions

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