SQL Server 2005 - Joining two tables and two columns

Question

In SQL Server 2005, by joining two tables on two columns, how do we get the value by matching first table two columns to the second table two columns and value zero for non-matching columns?

Below are the sample tables:

Table 1:

City    Vehicle      Count
---------------------------
London  Two wheeler  834
NewYork Four wheeler 60
NewYork Two wheeler  3
Sydney  Four wheeler 514
Sydney  Two wheeler  4551

Table 2:

City    Vehicle     Count
---------------------------
London  Two wheeler   5
NewYork Two wheeler   2
Sydney  Two wheeler  16

The expected output:

City    Vehicle      Count
---------------------------
London  Two wheeler    5
NewYork Four wheeler   0
NewYork Two wheeler    2
Sydney  Four wheeler   0
Sydney  Two wheeler   16

I did this successful on MS Excel using Pivot Table formula:

{=INDEX($L$6:$L$550,MATCH(F6,IF($K$6:$K$550=G6,$J$6:$J$550),0))}

Answer 1

You seek LEFT JOIN and COALESCE:

SELECT
    t1.city,
    t1.vehicle,
    COALESCE(t2.count,0) as count
FROM
    table_1 t1
LEFT JOIN table_2 t2 ON (t1.city = t2.city AND t1.vehicle = t2.vehicle)