Query on 2 tables do I need a 3rd? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query on 2 tables do I need a 3rd?

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-11-07 : 12:42:53

So so confused on how to fix this issue.

I have a query that takes info from a table called SABudget.

its Customer number, its Charge Customer the period and the amount. and another table called Customer that links the Customer number and allows me to match up the Address city st and zip. The issue is I also need the address for the Charge customer as well. Below is an example of query. I will also give you example of data and how i would like the data to look afterwards.

SELECT SaBudget.[Cust-no], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone, customer.[extra-field4], SaBudget.Amount, SaBudget.[Charge-cust], CONVERT(CHAR(10), SaBudget.CreateDate, 101) AS 'Create_Date', SaBudget.[sa-prd]
FROM customer AS customer INNER JOIN SaBudget AS SaBudget ON customer.[Cust-no] = SaBudget.[Cust-no]

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,10

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,11

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,12

Now notice that the customer number is 010268 and notice that the charge customer is 010268M. They are different They also have Different addresses. the Customer address is this 132 B St, Carlisle, PA, 17013-1907 the Charge customer address is this 444 B St, Carlisle, PA, 17013-1907. What I would like to see is the data show me the customers address and also the charge customer address on the same line. like this
010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,10, 444 B St, Carlisle, PA, 17013-1907

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,11, 444 B St, Carlisle, PA, 17013-1907

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,12, 444 B St, Carlisle, PA, 17013-1907

Those fields could be called charge_add, charge_st, charge_zip

shilpash
Posting Yak Master

103 Posts

Posted - 2012-11-07 : 13:25:24

So where is this 444 B St, Carlisle, PA, 17013-1907 coming from.I am guessing from SABudget.If so,simply add those columns--

SELECT SaBudget.[Cust-no]
,customer.name
,customer.Address
,customer.City
,customer.St
,customer.[Zip-code]
,customer.Telephone
,customer.[extra-field4]
,SaBudget.Amount
,SaBudget.[Charge-cust]
,charge_add,=SaBudget.ADDRESS
,charge_st=SaBudget.St
,charge_zip =SaBudget.[Zip-code]
,CONVERT(CHAR(10),SaBudget.CreateDate,101) AS 'Create_Date'
,SaBudget.[sa-prd]

FROM customer AS customer
INNER JOIN SaBudget AS SaBudget
ON customer.[Cust-no] = SaBudget.[Cust-no]

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-07 : 13:26:59

Join to the customer table again using Charge-cust = [Cust-no], if I am understanding correctly, that should give you the Charge Customer Address.

-Chad

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-11-07 : 14:09:15

I actually just did that lol. Now that I completed that they are asking for more and this part I am kinda confused. I think I might need to make a nested Select statement. I need to add another table that hold AR items. Now each Customer may have 50 records that are single line but I need to sum them up first and just need 2 fields from that table the Cust-no and the bal_amt. example
Table called AR_items
Data
Cust-No, bal_amt
010268, 57.00
010268, 57.00
010268, 103.00
010268, 234.00

I need these lines totaled up and then look like this.
010268, 451.00 so when I tie it to the query its already summed and will tie to the report.

SELECT SaBudget.[Cust-no], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone, customer.[extra-field4],
SaBudget.Amount, SaBudget.[Charge-cust], CONVERT(CHAR(10), SaBudget.CreateDate, 101) AS 'Create_Date', SaBudget.[sa-prd], customer_1.Address AS charge_add,
customer_1.City AS charge_city, customer_1.St AS charge_st, customer_1.[Zip-code] AS charge_zip, customer_1.name AS charge_name
FROM customer AS customer INNER JOIN
SaBudget AS SaBudget ON customer.[Cust-no] = SaBudget.[Cust-no] LEFT OUTER JOIN
customer AS customer_1 ON SaBudget.[Charge-cust] = customer_1.[Cust-no]

Now

shilpash
Posting Yak Master

103 Posts

Posted - 2012-11-07 : 14:20:32

WITH Totalbalamt_cte
AS (SELECT [Cust-no]
,SUM(bal_amt) bal_amt
FROM AR_items
GROUP BY [Cust-no]
)
SELECT SaBudget.[Cust-no]
,customer.name
,customer.Address
,customer.City
,customer.St
,customer.[Zip-code]
,customer.Telephone
,customer.[extra-field4]
,SaBudget.Amount
,SaBudget.[Charge-cust]
,CONVERT(CHAR(10),SaBudget.CreateDate,101) AS 'Create_Date'
,SaBudget.[sa-prd]
,customer_1.Address AS charge_add
,customer_1.City AS charge_city
,customer_1.St AS charge_st
,customer_1.[Zip-code] AS charge_zip
,customer_1.name AS charge_name
,Totalbalamt_cte.bal_amt

FROM customer AS customer
INNER JOIN SaBudget AS SaBudget
ON customer.[Cust-no] = SaBudget.[Cust-no]
LEFT OUTER JOIN customer AS customer_1
ON SaBudget.[Charge-cust] = customer_1.[Cust-no]
LEFT OUTER JOIN Totalbalamt_cte
ON Totalbalamt_cte.[Cust-no]=SaBudget.[Cust-no]

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-11-07 : 14:32:01

I actually created a view then linked it. Code looks like this.

SELECT SaBudget.[Cust-no], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone, customer.[extra-field4],
SaBudget.Amount, SaBudget.[Charge-cust], CONVERT(CHAR(10), SaBudget.CreateDate, 101) AS 'Create_Date', SaBudget.[sa-prd], customer_1.Address AS charge_add,
customer_1.City AS charge_city, customer_1.St AS charge_st, customer_1.[Zip-code] AS charge_zip, customer_1.name AS charge_name, ARBal.[bal-amt]
FROM customer AS customer INNER JOIN
SaBudget AS SaBudget ON customer.[Cust-no] = SaBudget.[Cust-no] LEFT OUTER JOIN
ARBal ON SaBudget.[Charge-cust] = ARBal.[Cust-no] LEFT OUTER JOIN
customer AS customer_1 ON SaBudget.[Charge-cust] = customer_1.[Cust-no]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources