JOIN without common columns

I am writing in reference to a previous post. I am trying to combine two tables that do not have a common column name but do share two columns with SOME common data.

I will explain in this example: Table A- AcctID, Table B- PartnerID, AcctValue

The column I am truly interested in is AcctValue which is in table B. I want to find the AcctValue for each AcctID. The problem is Partner does not match ALL AcctID only small portion.

There are 4,000 AcctID and they only match up with 2,000 PartnerID so I am not able to find the AcctValue for 2,000 AcctID.

This is what I've done but only shows me 2,000 AcctID:

Select tb1.Acctid, SUM(tb2.AcctValue) as AV
From A as tb1
JOIN B as tb2
ON tb1.acctid = tb2.partnerid

How can I get all the AcctId and their AcctValues? I am sure there is a way to do this but I can seem to figure it out

more ▼

asked Jul 28, 2011 at 08:21 AM in Default

sqlLearner 1 gravatar image

sqlLearner 1
792 34 38 45

@sqllearner If you have some accounts in A which do not have any matching records (by partner id) in table B then the SUM(tb2.AcctValue) for these is null. You can still list all 4,000 accounts by simply replacing the inner join with left join in your query. It also looks like you probably have zero to many relationships between tables A and B (otherwise why would you use the SUM). If this is the case then your query is also missing the GROUP BY:

    tb1.Acctid, SUM(tb2.AcctValue) as AV
    from A as tb1 left join B as tb2
        on tb1.acctid = tb2.partnerid
group by tb1.Acctid;
Jul 28, 2011 at 08:31 AM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest
If there is no column that reliably corresponds to AcctID then there is no way to reliably associate an AcctValue with an AcctID.
more ▼

answered Jul 28, 2011 at 08:24 AM

KenJ gravatar image

19.2k 1 3 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 28, 2011 at 08:21 AM

Seen: 2418 times

Last Updated: Jul 28, 2011 at 08:21 AM