On JOIN, show NULL when record is missing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

On JOIN, show NULL when record is missing

Author

Topic

CodeMonkey
Starting Member

1 Post

Posted - 2009-07-21 : 16:24:24

For example, if I have the following tables.

Vendors table:


VendorID  VendorName
--------- ------------------------------
V0001     Microsoft Corporation
V0002     Oracle Corporation
V0003     MySQL AB
V0004     PremiumSoft CyberTech Ltd.
V0005     FastReport Inc.
V0006     CoreLab
V0007     Mentat Technologies

Products table:


ProductID  VendorID  Description
---------- --------- -----------------------------------------
SDB0001    V0001     MS SQL Server Database 2005
SDB0002    V0002     Oracle Database 10g Enterprise
SDB0003    V0003     MySQL 5.0 Enterprise Edition
SDBA0001   V0004     Navicat For MySQL Administration
SDBA0002   V0007     Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   V0007     Dreamcoder 4.1 For MySQL Profesional
SDBA0004   V0007     Dreamcoder 4.1 For MS SQL Server Database

How could I return the following?


VendorID  VendorName                     ProductID  Description
--------- ------------------------------ ---------- -----------------------------------------
V0001     Microsoft Corporation          SDB0001    MS SQL Server Database 2005
V0002     Oracle Corporation             SDB0002    Oracle Database 10g Enterprise
V0003     MySQL AB                       SDB0003    MySQL 5.0 Enterprise Edition
V0004     PremiumSoft CyberTech Ltd.     SDBA0001   Navicat For MySQL Administration
V0005     FastReport Inc.                [NULL]     [NULL]
V0006     CoreLab                        [NULL]     [NULL]
V0007     Mentat Technologies            SDBA0002   Dreamcoder 4.1 For MySQL Enterprise
V0007     Mentat Technologies            SDBA0003   Dreamcoder 4.1 For MySQL Profesional
V0007     Mentat Technologies            SDBA0004   Dreamcoder 4.1 For MS SQL Server Database

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-21 : 16:41:34

SELECT A.VendorID,A.VendorNam,B.ProductID,B.Description
from Vendors A LEFT JOIN Products B on A.VendorID = B.VendorID