Designing a basic SQL query - Using join

rstols

Hi there,
Can anybody please help me compiling a basic query?

The query should filter for customers belonging to a certain posting group (Customer table) and then display the customer number along with the current customer balances.

I am not sure how to compile it since I need the data from 2 or maybe 3 tables:
Customer table - To identify the list of customers.
Cust Ledger Entry table - To get the 'Remaining Amount' for each customer. The challenge is that this field is a flowfield in this table and it is based upon entries in the 'Detailed Cust Ledger Entry' table.

Any suggestions or ideas...
Thank you

kine

You do not need to use Cust. Ledger Entry table, calc the sum directly from detailed entries like the standard flowfield on the customer card.

Just quickly designed query in query designer:

SELECT     [CRONUS International Ltd_$Customer].No_, [CRONUS International Ltd_$Customer].Name, [CRONUS International Ltd_$Customer].[Customer Posting Group], 
                      SUM([CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS Balance
FROM         [CRONUS International Ltd_$Customer] LEFT OUTER JOIN
                      [CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry] ON 
                      [CRONUS International Ltd_$Customer].No_ = [CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].[Customer No_]
GROUP BY [CRONUS International Ltd_$Customer].No_, [CRONUS International Ltd_$Customer].Name, [CRONUS International Ltd_$Customer].[Customer Posting Group]
HAVING      ([CRONUS International Ltd_$Customer].[Customer Posting Group] = 'DOMESTIC')

I am sure that this query could be optimized to not read all customers and limit the selection by WHERE clausule instead HAVING etc...

kriki

You reminded me to a page about joins I found once: I just put the link in the the SQL server Tips&Tricks forum : http://www.mibuso.com/forum/viewtopic.php?f=48&t=36599

rstols

Thanks for the quick response - you saved my day.

Rudy

rstols

I got the following message when designing the query. Do you have any idea why I got the message below:
'Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Visions$Detailed Cust_Ledg_ Entry.Amount(LCY)" could not be bound.'


SELECT
[Visions$Customer].No_, [Visions$Customer].Name, [Visions$Customer].[Customer Posting Group],
SUM
([Visions$Detailed Cust_Ledg_ Entry].[Amount(LCY)]) AS Balance
FROM
[Visions$Customer] LEFT OUTER JOIN
[Visions$Detailed Cust_ Ledg_ Entry]
ON
[Visions$Customer].No_ = dbo.[Visions$Detailed Cust_ Ledg_ Entry].[Customer No_]


Thank you
Rudy

garak

rstols wrote:

I got the following message when designing the query. Do you have any idea why I got the message below:
'Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Visions$Detailed Cust_Ledg_ Entry.Amount(LCY)" could not be bound.'


SELECT
[Visions$Customer].No_, [Visions$Customer].Name, [Visions$Customer].[Customer Posting Group],
SUM
([Visions$Detailed Cust_Ledg_ Entry].[Amount(LCY)]) AS Balance
FROM
[Visions$Customer] LEFT OUTER JOIN
[Visions$Detailed Cust_ Ledg_ Entry]
ON
[Visions$Customer].No_ = dbo.[Visions$Detailed Cust_ Ledg_ Entry].[Customer No_]


Thank you
Rudy

There is a "Space" missed in your SUM statemend behind the first "underline".
U wrote "Detailed Cust_Ledg_ Entry". The correct name is: "Detailed Cust_ Ledg_ Entry".
Also u muss set the Space before the (LCY) in your Sum statement. The correct name is: [Amount (LCY)])

SELECT 
	[Visions$Customer].No_, [Visions$Customer].Name, [Visions$Customer].[Customer Posting Group],
SUM
	([Visions$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS Balance
FROM 
	[Visions$Customer] LEFT OUTER JOIN 
	[Visions$Detailed Cust_ Ledg_ Entry] 
ON	
	[Visions$Customer].No_ = dbo.[Visions$Detailed Cust_ Ledg_ Entry].[Customer No_]

And you should use a "group by" statement. Then i'm sure, you need a sum per Customer and Posting group.

Try:

SELECT 
	[Visions$Customer].No_, [Visions$Customer].Name, [Visions$Customer].[Customer Posting Group],
SUM
	([Visions$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS Balance
FROM 
	[Visions$Customer] LEFT OUTER JOIN 
	[Visions$Detailed Cust_ Ledg_ Entry] 
ON	
	[Visions$Customer].No_ = dbo.[Visions$Detailed Cust_ Ledg_ Entry].[Customer No_]
group by
        [Visions$Customer].No_, 
        [Visions$Customer].Name, 
        [Visions$Customer].[Customer Posting Group]

Regards