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

avatar image

sqlLearner 1
972 43 51 57

@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

1 answer: sort voted first

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

avatar image

25k 3 13 20

(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.

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: 4627 times

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

Copyright 2018 Redgate Software. Privacy Policy