1

I have two table lets say Table1 and Table2 and both table have a column Name and ID

I want to fetch all the name from Table2 which is not in Table1 I have written below query :

Select Name,ID FROM Table2 t2 WHERE t2.Name NOT IN (Select t1.Name FROM Table1 t1);

but this query is not giving expected output, I mean it also gives me Name which is in Table1.

I done some google and found it could be if Table1 have null values i.e. If sub-query result contain null. although my table doesn't have null values but for making sure this is not a problem I have rewritten query, but without ant luck:

Select Name,ID FROM Table2 t2 WHERE t2.Name 
NOT IN (Select t1.Name FROM Table1 t1 where t1.Name is not null);

Still I am not getting expected result but strangely some time it gives result as expected and some time not, any suggestion, help will be appreciated.

Is there any bug in sybase 16? I am using sybase 16 with db-visualizer.

UPDATE If I have below data

Table1

   Name        ID
-------------------
  Jay          1
---------------------

Table2

   Name        ID
-------------------
  Jay          1
  Tom          2
---------------------

Expected result

   Name        ID
-------------------
  Tom          2
---------------------

but I am getting :

Result coming

   Name        ID
-------------------
  Jay          1
  Tom          2
---------------------

Please note some time I am getting expected result too, not sure why some time it is working as expected and some time not

user3717431
  • 103
  • 2
  • 9
  • Pls be a bit more specific as to what you mean by not getting the expected result! Provide sample data, expected output and the output provided by the 2nd query. – Shadow Apr 28 '16 at 19:10
  • 1
    [edit] your question and add some sample data (as `insert into`) and what you expect to see. –  Apr 28 '16 at 19:10
  • 4
    Maybe you have some trailing spaces in one of the names. Try with `trim(name)` (or whatever the equivalent in Sybase is) –  Apr 28 '16 at 19:35
  • 1
    Very unlikely to be a bug in ASE 16, this is absolutely standard functionality; I used this type of query myself recently and it worked just fine. In the absence of an exact reproduction that others can also repro, I would assume there is something funny with your query.-- upper/owercase mismatches? Funny characters (e.g. CR or LF) in the data? – RobV Apr 29 '16 at 00:25

0 Answers0