MS SQL query to keep all records of one table

I need to join two tables, one table is called budget which has 4 colummns: clientNo, ClientName, budge, Sales
another table is call profit which has 3 columns:  fcustno, fsalespn, profit.
I need to join two tables together base on clientNo and sales,  but i need keep all records in budget table.

the queries I use is
select * from budget  A
left join profit B  on b.fsalespn = A.sales_rep and B.fcustno = A.client_no

but it didn't get all records from budget table.

how do I achieve it? thanks
HemlockPrintersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FunkmotorCommented:
Okay, here we go...sorry about that earlier flub.

I think what you need is not just a left join, but a left outer join.

It will keep all the records from the left table and add those from the right table that have matches,

This page has a good description and examples, so I won't cut & paste:
     http://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx

I hope that does what you need - joins can be tricky!
0
Brian CroweDatabase AdministratorCommented:
LEFT JOIN and LEFT OUTER JOIN are exactly the same thing.  The following queries should get you the same number.

SELECT COUNT(1) FROM budget

SELECT DISTINCT COUNT(B.<primary key column>)
FROM budget AS A
LEFT OUTER JOIN profit AS B
   ON B.fsalespn = A.sales_rep
   AND B.fcustno = A.client_no
0
HemlockPrintersAuthor Commented:
Hi Bricrowe,


SELECT COUNT(1) FROM budget, the result is 92,

SELECT DISTINCT COUNT(B.<primary key column>)
FROM budget AS A
LEFT OUTER JOIN profit AS B
   ON B.fsalespn = A.sales_rep
   AND B.fcustno = A.client_no

the result is 54.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Brian CroweDatabase AdministratorCommented:
Just checkign something...what is the value of

SELECT DISTINCT COUNT(<primary key column>) FROM budget
0
SharathData EngineerCommented:
I did not see any issue with your original LEFT JOIN query.

select * from budget  A
left join profit B  on b.fsalespn = A.sales_rep and B.fcustno = A.client_no

How can you say that its not giving all records from budget table?
Are you missing any sales_rep from budget table? Can you post some sample data, result of your query and the expected result.
0
SharathData EngineerCommented:
<< SELECT COUNT(1) FROM budget, the result is 92,

SELECT DISTINCT COUNT(B.<primary key column>)
FROM budget AS A
LEFT OUTER JOIN profit AS B
   ON B.fsalespn = A.sales_rep
   AND B.fcustno = A.client_no

the result is 54.>>

This is expected if you do not have all fsalespn/fcustno in profit table. You are counting the B records not A. change the count to A and you will get same no.of records.

SELECT COUNT(A.<primary key column>)
FROM budget AS A
LEFT OUTER JOIN profit AS B
   ON B.fsalespn = A.sales_rep
   AND B.fcustno = A.client_no
0
Brian CroweDatabase AdministratorCommented:
Thank you Sharath, typo on my part was wondering how he could possibly get the results he did.
0
Scott PletcherSenior DBACommented:
The LEFT OUTER JOIN will include all rows from the first (left) table in the join.

COUNT() ignores NULL values.  In a LOJ, if the second (right) table in the join does not have a matching row, SQL automatically sets all column values in the second/missing table to NULL.
0
HemlockPrintersAuthor Commented:
I am very confused.
the results of inner join and left join are the same
0
HemlockPrintersAuthor Commented:
What is the difference between the following queries?

select * from budget  A
left join profit B  on b.fsalespn = A.sales_rep and B.fcustno = A.client_no

select * from budget A
left join (SELECT * from profit  ) as b
on b.fsalespn = A.sales_rep and B.fcustno = A.client_no
0
SharathData EngineerCommented:
No difference.
0
HemlockPrintersAuthor Commented:
the result is different.
the second one retuned more rows than the first one
0
PortletPaulfreelancerCommented:
a:
LEFT JOIN = LEFT OUTER JOIN
('left join' is just an allowed abbreviation for 'left outer join')
b:
Your use of left join from budget to profit will list all budgets.
I think you are mis-interpreting the results of those counts.
c:
select * from budget  A left join profit B
   on b.fsalespn = A.sales_rep --<< a.sales_rep isn't listed as a field of Budget
   and B.fcustno = A.client_no

from question:
  budget (clientNo, ClientName, budge, Sales)

  profit (fcustno, fsalespn, profit)

I assume therefore that Sales is actually Sales_Rep (and ClientNo id Client_No)
d:
Please try this it will, I hope, help identify how counts are affected by records that don't match between the 2 tables:
select
          count(a.Client_No)                                                as budget_clients
        , count(B.fcustno)                                                  as profit_clients_raw
        , count(case when B.fcustno IS NULL a.Client_No else B.fcustno end) as profit_clients_amended
        , count(case when B.fcustno IS NOT NULL then 1 end)                 as profit_is_not_null
        , count(case when B.fcustno IS NULL then 1 end)                     as profit_is_null
        , count(DISTINCT a.Client_No)                                       as budget_distinct_clients
        , count(DISTINCT B.fcustno)                                         as profit_distinct_clients_raw
from budget  A
left join profit B
   on b.fsalespn = A.sales_rep --<< a.sales_rep isn't listed as a field of Budget
   and B.fcustno = A.client_no;

Open in new window

e:
If you are  still unsure, providing us with some sample data would help us to help you.
0
HemlockPrintersAuthor Commented:
thanks PortletPaul.

here is the result:

budget_clients:126
profit_clients_raw : 54
profit_clients_amended: 126
profit_is_not_null:54      
profit_is_null:72
budget_distinct_clients:92
profit_distinct_clients_raw:20
0
PortletPaulfreelancerCommented:
profit_is_not_null: 54   +  
profit_is_null:        72
=
budget_clients:  126
=
profit_clients_amended: 126

so the left join is
listing all records from budget (126)
and all matches to profit (54)
and un-matched to profit is (72)

There happen to be 92 customers referenced in the budget table
but only 20 of those customers are referenced in the profit table

{+edit}

comparing the above to this:
SELECT COUNT(1) FROM budget, the result is 92,

The joining of those 2 tables is multiplying the number of rows (from 92 to 126)

IF there is supposed to be a one-to-one relationship between those tables then there is something missing in the join logic.
0
PortletPaulfreelancerCommented:
Would you please try this as a test?
select
          count(a.Client_No)                                                as budget_clients
        , count(B.fcustno)                                                  as profit_clients_raw
        , count(case when B.fcustno IS NULL a.Client_No else B.fcustno end) as profit_clients_amended
        , count(case when B.fcustno IS NOT NULL then 1 end)                 as profit_is_not_null
        , count(case when B.fcustno IS NULL then 1 end)                     as profit_is_null
        , count(DISTINCT a.Client_No)                                       as budget_distinct_clients
        , count(DISTINCT B.fcustno)                                         as profit_distinct_clients_raw
from budget  A
left join profit B
   on B.fcustno = A.client_no
;

Open in new window

0
HemlockPrintersAuthor Commented:
here is the result:

budget_clients:323
profit_clients_raw : 253
profit_clients_amended: 323
profit_is_not_null:253      
profit_is_null:70
budget_distinct_clients:92
profit_distinct_clients_raw:22
0
PortletPaulfreelancerCommented:
OK, that's worse - are you expecting a one-to-one relationship between those tables?

If so, something is missing in the join, but this is all we know about those tables:

 budget (Client_No, Sales_Rep, ClientName, budge)

 profit (fcustno, fsalespn, profit)
0
HemlockPrintersAuthor Commented:
in the  Budget table, there are rows with same client_no but differs Sales_Rep.

there are more than those fields in two tables.
0
PortletPaulfreelancerCommented:
Not sure what to say at this point.

IF you are expecting a total of 92 rows when budget is joined to profit, then something is missing (but at this end of the browser we don't know what that is).

IF the join you have provided is correct then 126 rows in total is correct, and all budget's are being reported (i.e. budget_distinct_clients:92 )

But I get the feeling you are not content, so for us to help you further I would suggest:
a. giving us the complete definition of those tables
b sample data*
c. expected results**

* anything private could be scrubbed e.g. client names, budget amounts could be set to (say) 1000 etc.
** based on the scrubbed data (e.g. totals affected by setting budget=1000)
0
HemlockPrintersAuthor Commented:
thanks PortletPaul.

Please check the attached file.
the result I expect is to keep all records from sheet1 which is budget table and sheet2 profit.
it should be full join, but the rows total of  full join is only 57.
Book3.xls
0
PortletPaulfreelancerCommented:
In the spreadsheet there are 3 client references in Profit that don't exist in Budget
so:
select count(*) from Budget
;
SELECT
      COALESCE(b.client_no,p.fcustno)  AS client_no
    , COALESCE(b.sales_rep,p.fsalespn) AS sales_rep
    , count(*)                         AS count_star
    , count(b.client_no)               AS count_B
    , count(p.fcustno)                 AS count_P
FROM Budget AS B
FULL OUTER JOIN Profit AS p
             ON b.client_no = p.fcustno
            AND b.sales_rep = p.fsalespn

WHERE b.client_no IS NULL --<<<<<<<<<<<<<<<<<<

GROUP BY
      COALESCE(b.client_no,p.fcustno)
    , COALESCE(b.sales_rep,p.fsalespn)
;

select '92 + 3 = 95'
;

SELECT
      COALESCE(b.client_no,p.fcustno)  AS client_no
    , COALESCE(b.sales_rep,p.fsalespn) AS sales_rep
    , count(*)                         AS count_star
    , count(b.client_no)               AS count_B
    , count(p.fcustno)                 AS count_P
FROM Budget AS B
FULL OUTER JOIN Profit AS p
             ON b.client_no = p.fcustno
            AND b.sales_rep = p.fsalespn
GROUP BY
      COALESCE(b.client_no,p.fcustno)
    , COALESCE(b.sales_rep,p.fsalespn)
;
	

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.