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.

Joining a query (inner/outer)

I have this current sql query which works great, but it only returns
the rows that exist in all tables.
I need to return all rows from po_cfg_Budget B and simply fill in 0 if
there are no totals.
Any help would be greatly appreciated.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S,
(select * from po_cfg_budget) B,
(Select * from po_cfg_dept) D
where S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
and S.DeptID = D.BudgetDeptID
order by B.DeptIDThis might work for you (passes syntax check but not actually
tested)...
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S
left outer join po_cfg_budget B on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D on S.DeptID = D.BudgetDeptID
order by B.DeptID
If you have null dollar amounts you can wrap those in the ISNULL or
COALESCE function.
Bryce|||Not entirely sure what you are looking for but try this.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
coalesce((B.Budget - NotApproved - Approved), 0) as AmountRemaining,
coalesce((B.Budget - Approved),0) as AmountApprovedRemaining
from po_cfg_budget B
left outer join BudgetStatus S
on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D
on D.BudgetDeptID = S.DeptID
order by B.DeptID
The inline views that you were using were redundant.
"Dave" wrote:

> I have this current sql query which works great, but it only returns
> the rows that exist in all tables.
> I need to return all rows from po_cfg_Budget B and simply fill in 0 if
> there are no totals.
> Any help would be greatly appreciated.
> select S.*,
> b.Budget,
> B.Description,
> D.DeptID as DeptDescription,
> (B.Budget - NotApproved - Approved) as AmountRemaining,
> (B.Budget - Approved) as AmountApprovedRemaining
> from BudgetStatus S,
> (select * from po_cfg_budget) B,
> (Select * from po_cfg_dept) D
> where S.Years = B.BudgetYear
> and S.Months = B.BudgetMonth
> and S.DeptID = B.DeptID
> and S.AccountID = B.AccountID
> and S.DeptID = D.BudgetDeptID
> order by B.DeptID
>

JOIN to get rows in one table that are not in the other

I've got two identical tables: sw_scan and software.
Now I want to get all rows from sw-scan that don't exist in table software.

With my statement I get all of the rows from table 1 with NULL for all table 2 values.. :confused:

SELECT *
FROM sw_scan
LEFT JOIN software USING(Manufacturer,Product,Version,Computer)
WHERE software.manufacturer IS NULL;

oh, and (Manufacturer, Product, Version, Computer) is primary key in both tables ..well, that should work, the only thing i can suggests is don't use USING
SELECT *
FROM sw_scan
LEFT
JOIN software
on sw_scan.Manufacturer
= software.Manufacturer
and sw_scan.Product
= software.Product
and sw_scan.Version
= software.Version
and sw_scan.Computer
= software.Computer
WHERE software.Manufacturer IS NULL|||hmm.. thanks..
but I already tried lots of things.. to be sure.. and this was one of them...
sth really weird is going on here.. :(|||well, the table 2 columns should all be null, because that's what you're looking for, right?

by the way, what database is this?|||They should be NULL... but the resultset should only contain the rows that have NULL at the software.Manufacturer field after the JOIN.
I used exactly the smae statements to create and fill the tables, only changing the table name, and afterwards I deleted 10 rows from software...
So my resultset should have ten rows. Instead it outputs all 1000 rows filling all fields from table software with NULL.

It's a MySQL 4 db|||do you get all 990 rows when you change LEFT to INNER?|||When I make it an INNER JOIN, I get an empty set..
And if I also get rid of the WHERE clause I get the 990 rows...|||i'm running out of ideas

try REPAIRing the database|||Solved !

After I dropped the tables.. recreated them (with exactly the same statement) and filled them again, it worked..

thnx for the help Rudy!