#AcumaticaTnT Joining Tables Together

Joining tables together in SQL is like doing a VLOOKUP in Excel.

In general, when doing a join, you want to include all of the columns that are in the primary key of the table that you are joining to.

When you look at the Customer Data Access Class (DAC) in Acumatica, it automatically gets the Customer ID and Customer Name for you.

In the database though, Customer ID and Customer Name are in the BAccount table which stores IDs and Names for Customers, Vendors, Employees, and Branches.

Since Customer ID and Customer Name are in the BAccount table, we need to join from Customer to BAccount to get that info.

Since BAccount is the table that we're joining to, then we need to know which columns are in the primary key.

We can find out which columns are in the primary keys by looking at the COLUMN_NAME column in the following query:

sp_pkeys 'BAccount'

As you can see in the previous screenshot, CompanyID and BAccountID are in the primary key, so we need to include both of those columns when joining to the BAccount table.

Spoiler alert: CompanyID is pretty much always in the primary key, just like we pretty much always need to include CompanyID when querying the database as discussed in this post:

https://www.augforums.com/forums/augsql/your-first-select-statement-and-always-using-companyid

I'm going to start from the Customer table and join to the BAccount table using a LEFT JOIN. For the first column, we need to use the ON statement, then use the AND statement for every column after that.

I like putting each column on a separate line because I think it's easier to read that way. But you can put them all on one line if you want.

Also, when doing joins, we then need to reference the table whenever we're referencing a column from that table. In order to make it less "wordy" so we don't have to repeat long table names every time, we can put a short alias after each table. I try to use just one letter whenever possible to make the alias as short as possible. I'll use C for Customer and B for BAccount.

SELECT *
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2

SELECT * means that we're getting all columns from both tables. If we only want the AcctCD and AcctName columns from the BAccount table, but all columns from the Customer table, then we can change SELECT * to SELECT B.AcctCD, B.AcctName, C.* like this:

SELECT B.AcctCD, B.AcctName, C.*
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2

We can also alias the column names. Since AcctCD is Customer ID and AcctName is Customer Name, we can alias them on the first line of the SELECT like this:

SELECT B.AcctCD 'Customer ID', B.AcctName 'Customer Name', C.*
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2