What is the correct Select Statement for this?

Question

//This is my Table 1

    TransactionNum     Type  
    65658               0  
    65659               0  
    65660              449  
    65661               0  

//This is My Table 2

    Type    Description          
    445 Discount #1  
    446 Discount #2  
    447 Discount #3  
    448 Discount #4  
    449 Discount #5  
    450 Discount #6  

//This is My Script

    SELECT a.TransactionNum,b.Description FROM Table1 a,Table2 b
    WHERE a.Type=b.Type ORDER BY TransactionNum  

//Result

    TransactionNum   Description
    65659            Discount #4

//I want the result to be like this, The TransactionNum with 0 type should also be included in the result, Please help me with this, Im using SQL2000 here.

    TransactionNum      Description   
    65658               0  
    65659               0  
    65660              Discount #5  
    65661               0

Answer 1

use LEFT JOIN instead,

SELECT a.TransactionNum, 
       COALESCE(b.Description, CAST (a.Type AS VARCHAR(20))) AS Description
FROM   Table1 a LEFT JOIN Table2 b
          ON a.Type=b.Type
ORDER  BY a.TransactionNum

• SQLFiddle Demo

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

OUTPUT

╔════════════════╦═════════════╗
║ TRANSACTIONNUM ║ DESCRIPTION ║
╠════════════════╬═════════════╣
║          65658 ║ 0           ║
║          65659 ║ 0           ║
║          65660 ║ Discount #5 ║
║          65661 ║ 0           ║
╚════════════════╩═════════════╝