How to join two sql tables when the common column has different names but information is the same in both tables
Solution 1
SELECT [Bill-to Customer No_], [Invoice Amount] AS amt, [Name]
FROM Table1 t1 JOIN Table2 t2
ON t1.[Bill-to Customer No_] = t2.[No_]
ORDER BY amt DESC;
I haven't grasped your column names yet, but hope you get the idea.
EDIT : (as per your new query)
SELECT [Sell-to Customer No_], [Name], SUM([Amount]) as "Total Dollars Spent"
FROM [Table 1 - LIVE$Sales Invoice Line] a JOIN [Table 2 - LIVE$Customer] b
ON a.[Sell-to Customer No_] = b.[No_]
WHERE [Source Code] = 'RENTAL' and [Sell-to Customer No_] != 'GOLF'
GROUP BY [Sell-to Customer No_], [Name]
ORDER BY SUM([Amount]) DESC;
You need to add [Name]
to the GROUP BY
clause as well. Remember you cannot SELECT
a column that's not a part of GROUP BY
unless it's being processed by a group function like [Amount]
is being processed by SUM()
.
Solution 2
SELECT
[bill-to Customer No_]
,customer_name
FROM table1 AS a
INNER JOIN table2 AS b on a.[bill-to Customer No_]=b.No_
Solution 3
select Bill-to, CustomerNo_ ,customer_name
from Table1 a
join Table2 b on a.CustomerNo_ = b.No_
Craig Zirnheld
Updated on July 23, 2022Comments
-
Craig Zirnheld almost 2 years
Relatively new to SQL querying. I can successfully get results from a simple query that shows a customer number and a total dollars invoiced sorted highest dollar amount to lowest. I want to also display the customer name. The customer name,
[Name]
, is in another table along with the customer number but the column name for customer number is different, ie.Table 1
is[Bill-to Customer No_]
andTable 2
is just[No_]
. How would I get the information fromTable 2
to display in the same row with the customer number?