Solved

SQL Link SQL Statement to table to get a name

Posted on 2014-09-15
4
148 Views
Last Modified: 2014-09-16
Hi
How do I alter the following SQL so that it links to a table called Customers to get [Customer Name]
(The Table Customers has CustomerID,[Customer Name]....)

select
 CustomerID, sum(amount) amount
 from
  (
    select [Link ID] CustomerID, Amount * -1 As Amount from payments
    union all
    select customerId, [inv amt] from invoices
 ) SubQuery
 group by CustomerID
0
Comment
Question by:murbro
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40323896
select
  SubQuery.CustomerID, c.[Customer Name], sum(SubQuery.amount) amount
  from
   (
     select [Link ID] CustomerID, Amount * -1 As Amount from payments
     union all
     select customerId, [inv amt] from invoices
  ) SubQuery
inner join customers c on
    c.CustomerID = SubQuery.CustomerID
group by SubQuery.CustomerID
0
 

Author Closing Comment

by:murbro
ID: 40323991
Thanks very much
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40324098
You're welcome.

D'OH, I didn't add the name to the GROUP BY (just to be clear to anyone else looking at this):

group by SubQuery.CustomerID, c.[Customer Name]
0
 

Author Comment

by:murbro
ID: 40325153
Thanks. Picked that up. appreciate the help
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now