MSSQL LEFT OUTER JOIN easier ways

Question

I have 2 tables in ms-sql 2008

Data_CardCustomer
It has >> CustomerId,Code,Name, ... etc fields

Data_ActionPriceList
It has >> CustomerId,StockId,Price1,Price2,Price3 fields

I want to aggregate all price whith all customer records.
If customerId does not have a price in the DataActionPriceList table, it shall return zero.

Code,Price1,Price2,Price3

Current Query

SELECT Code,
Isnull((select Price1 from Data_ActionPriceList where Data_ActionPriceList.CustomerId=Data_CardCustomer.CustomerId and StockId=10005 ),0) As price1,
Isnull((select Price2 from Data_ActionPriceList where Data_ActionPriceList.CustomerId=Data_CardCustomer.CustomerId and StockId=10005 ),0) As price2, 
Isnull((select Price3 from Data_ActionPriceList where Data_ActionPriceList.CustomerId=Data_CardCustomer.CustomerId and StockId=10005 ),0) As price3 
FROM Data_CardCustomer

is there easier ways?

Answer 1

You're right on the way with your idea in your question's title.

SELECT
    dcc.Code,
    Isnull(dapl.Price1, 0) As price1,
    Isnull(dapl.Price2, 0) As price2,
    Isnull(dapl.Price3, 0) As price3

FROM
    Data_CardCustomer dcc LEFT JOIN Data_ActionPriceList dapl ON dapl.CustomerId = dcc.CustomerId

AND dapl.StockId=10005

Please note: you may also use JOIN instead of LEFT JOIN, depending on your expected results. With JOIN you'll get only rows with corresponding entries in both of your tables, whereas you'll retrieve all rows from Data_CardCustomer using a LEFT JOIN.

Answer 2

SELECT Data_CardCustomer.Code,
    Isnull(Data_ActionPriceList.Price1 ,0) As price1,
    Isnull(Data_ActionPriceList.Price2 ,0) As price2, 
    Isnull(Data_ActionPriceList.Price3 ,0) As price3 
FROM Data_CardCustomer 
left join Data_ActionPriceList on Data_ActionPriceList.CustomerId=Data_CardCustomer.CustomerId and StockId=10005