CODEWITHSUNDEEP
t-sqlsybase
Qasim0787
Qasim0787

Reputation: 301

In T_SQL Null not allowed

I have two tables in Sybase
Table1 

ID   NAME   PRICE
123  KING   12.23
234  KONG   23.43

Table2  
ID  IND  CD
123 1    A

 When we do    "Desc Table2"

Column Name    Data Type   Null   Default Value
ID             Int         N          
IND            BIT         N           0
CD             CHAR        Y

So, table2 has a column that could never be null and has default value as 0. when I join both tables

select t1.*, t2.IND, t2.CD
from Table1 t1, Table2 t2
where  t1.ID *= t2.ID

OUTPUT: 

ID   NAME   PRICE  IND  CD
123  KING   12.23  1    A
234  KONG   23.43  0

Desired output I want to show null values for the fields those are not found in table2. when I try to update to make it null than it give me message NULL not allowed. It looks like Table2.IND field was setup initially as not null allowed.

OUTPUT:

ID   NAME   PRICE  IND  CD
123  KING   12.23  1    A
234  KONG   23.43

how to change this thing in my query so, I could see NULL if data not exist in table2.

Upvotes: 0

Views: 67

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

What you see in the result of your query is how LEFT JOIN (or 20 year old syntax *=) works.

If there is a row in t1 with some ID and t2 doesn't have a row with the same ID, then 

t1 LEFT JOIN t2 ON t1.ID=t2.ID

would return this row from t1 and NULL values for t2 columns, because there are no corresponding values in t2.

Does Sybase have a CASE statement? Most likely it has. You can use it to replace such NULL values with 0, or whatever you need.

select 
    t1.*
    ,CASE WHEN t2.IND IS NULL THEN 0 ELSE t2.IND END AS IND
    ,t2.CD
from Table1 t1, Table2 t2
where  t1.ID *= t2.ID

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You should use LEFT JOIN:

SELECT t1.ID, t1.NAME, t1.PRICE, t2.IND, t2.CD
FROM Table1 t1
LEFT JOIN Table2 t2 
  ON t1.ID = t2.ID;

SqlFiddleDemo

Output:

╔══════╦═══════╦════════╦═════════╦════════╗
║ ID   ║ NAME  ║ PRICE  ║  IND    ║   CD   ║
╠══════╬═══════╬════════╬═════════╬════════╣
║ 123  ║ KING  ║ 12.23  ║ 1       ║ A      ║
║ 234  ║ KONG  ║ 23.43  ║ (null)  ║ (null) ║
╚══════╩═══════╩════════╩═════════╩════════╝

Upvotes: 1

Related Questions