Joining when rows don't exist.

Afternoon all..
I'm trying to join two tables together. The tables have information
like:
Table_Data
Name - Calls - Sales
Bob -- 17 -- 10
John -- 23 -- 5
Dave -- 25 -- 7
Carol -- 16 -- 13
Table_Target_Data
Name - Calls - Sales
Bob -- 30 -- 20
Carol -- 40 -- 30
What I need to do is to join them together, but also include those
people that aren't mentioned in the target table.
So far I have :
SELECT
T1.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1,Table_Data T2
where T1.Name = T2.Name
Group by T1.Name
This will only bring back those names that are in both tables. How can
I bring back 'John' and 'Dave', filling in relevant results with <null>
or '0'?Hi John,
SELECT
T1.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1
LEFT JOIN Table_Data T2
ON T1.Name = T2.Name
HTH, Jens Suessmeyer.|||You need to use left join and you're using inner join.
MC
<vladikavkaz@.XXXXyou.co.uk> wrote in message
news:1139227585.567641.110490@.g44g2000cwa.googlegroups.com...
> Afternoon all..
> I'm trying to join two tables together. The tables have information
> like:
> Table_Data
> Name - Calls - Sales
> Bob -- 17 -- 10
> John -- 23 -- 5
> Dave -- 25 -- 7
> Carol -- 16 -- 13
> Table_Target_Data
> Name - Calls - Sales
> Bob -- 30 -- 20
> Carol -- 40 -- 30
> What I need to do is to join them together, but also include those
> people that aren't mentioned in the target table.
> So far I have :
> SELECT
> T1.Name AS [Agent Name],
> T1.Calls AS [Target Client Calls],
> T2.Calls AS [Client Calls],
> T1.Sales AS [Target Sales],
> T2.Sales AS [Sales]
> FROM
> Table_Target_Data T1,Table_Data T2
> where T1.Name = T2.Name
> Group by T1.Name
> This will only bring back those names that are in both tables. How can
> I bring back 'John' and 'Dave', filling in relevant results with <null>
> or '0'?
>|||vladikavkaz@.XXXXyou.co.uk wrote on 6 Feb 2006 04:06:25 -0800:

> Afternoon all..
> I'm trying to join two tables together. The tables have information
> like:
> Table_Data
> Name - Calls - Sales
> Bob -- 17 -- 10
> John -- 23 -- 5
> Dave -- 25 -- 7
> Carol -- 16 -- 13
> Table_Target_Data
> Name - Calls - Sales
> Bob -- 30 -- 20
> Carol -- 40 -- 30
> What I need to do is to join them together, but also include those
> people that aren't mentioned in the target table.
> So far I have :
> SELECT
> T1.Name AS [Agent Name],
> T1.Calls AS [Target Client Calls],
> T2.Calls AS [Client Calls],
> T1.Sales AS [Target Sales],
> T2.Sales AS [Sales]
> FROM
> Table_Target_Data T1,Table_Data T2
> where T1.Name = T2.Name
> Group by T1.Name
> This will only bring back those names that are in both tables. How can
> I bring back 'John' and 'Dave', filling in relevant results with <null>
> or '0'?
Use an outer join.
SELECT
T2.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1 RIGHT OUTER JOIN Table_Data T2
ON T1.Name = T2.Name
Group by T2.Name
If you want 0 in the target data rather than nulll, use
SELECT
T2.Name AS [Agent Name],
COALESCE(T1.Calls,0) AS [Target Client Calls],
T2.Calls AS [Client Calls],
COALESCE(T1.Sales,0) AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1 RIGHT OUTER JOIN Table_Data T2
ON T1.Name = T2.Name
Group by T2.Name
Dan|||Thanks all for the help.
Got the results I need now.