[Resolved] Need Help with this SQL Statement
-
Dim strSql As String = "SELECT DISTINCT Orders.OrderId, Orders.ClientId, OrderDate, DueDate, SubTotal, SalesTax, CityTax, LaborTax, Discount, Delivery, OrderTotal,
OrderDetails.ItemId, OrderDetails.Category, OrderDetails.Description, OrderDetails.Qnty, OrderDetails.Unit, OrderDetails.RetailPrice, OrderDetails.Total, (Select MIN (OrderPymts.PrevBal)) As PrevBal, (Select MAX(OrderPymts.PaymentDate)) As PaymentDate,
(Select MIN (OrderPymts.Payment)) As Payment, (Select MIN (OrderPymts.Balance)) As Balance, OrderPymts.Terms, (Select MAX(OrderPymts.PaymentId)) As PaymentId, Clients.Name,
Clients.Address + ' ' + Clients.City + ', ' + Clients.State + '. ' + Clients.Zip AS ClientsAddress,
'Phone : ' + Clients.Phone + ' Fax : ' + Clients.Fax + ' CellPhone : ' + Clients.CellPhone + ' Email : ' + Clients.Email As ContactInfo, Clients.Since
FROM Orders, Clients
INNER JOIN OrderDetails ON OrderDetails.OrderId = Orders.OrderId
INNER JOIN Clients ON Orders.ClientId = Clients.ClientId
INNER JOIN OrderPymts ON OrderPymts.OrderId = Orders.OrderId
WHERE (Orders.OrderId = @OrderId)"I'm having trouble with this statement. This statement is used to call the records in the invoice. Odd since I have 2 records, One with multiple items and only 1 payment and it displays fine. The other record with multiple detail items and multiple payments displays the items twice on the invoice report and shows the first payment record instead of the latest payment record. There are 3 tables, Orders, OrderDetails, and OrderPymts. So I'm trying to query from all three tables based on the orderId. Any Help would be greatly appreciated.
-
Dim strSql As String = "SELECT DISTINCT Orders.OrderId, Orders.ClientId, OrderDate, DueDate, SubTotal, SalesTax, CityTax, LaborTax, Discount, Delivery, OrderTotal,
OrderDetails.ItemId, OrderDetails.Category, OrderDetails.Description, OrderDetails.Qnty, OrderDetails.Unit, OrderDetails.RetailPrice, OrderDetails.Total, (Select MIN (OrderPymts.PrevBal)) As PrevBal, (Select MAX(OrderPymts.PaymentDate)) As PaymentDate,
(Select MIN (OrderPymts.Payment)) As Payment, (Select MIN (OrderPymts.Balance)) As Balance, OrderPymts.Terms, (Select MAX(OrderPymts.PaymentId)) As PaymentId, Clients.Name,
Clients.Address + ' ' + Clients.City + ', ' + Clients.State + '. ' + Clients.Zip AS ClientsAddress,
'Phone : ' + Clients.Phone + ' Fax : ' + Clients.Fax + ' CellPhone : ' + Clients.CellPhone + ' Email : ' + Clients.Email As ContactInfo, Clients.Since
FROM Orders, Clients
INNER JOIN OrderDetails ON OrderDetails.OrderId = Orders.OrderId
INNER JOIN Clients ON Orders.ClientId = Clients.ClientId
INNER JOIN OrderPymts ON OrderPymts.OrderId = Orders.OrderId
WHERE (Orders.OrderId = @OrderId)"I'm having trouble with this statement. This statement is used to call the records in the invoice. Odd since I have 2 records, One with multiple items and only 1 payment and it displays fine. The other record with multiple detail items and multiple payments displays the items twice on the invoice report and shows the first payment record instead of the latest payment record. There are 3 tables, Orders, OrderDetails, and OrderPymts. So I'm trying to query from all three tables based on the orderId. Any Help would be greatly appreciated.
We can't see the structure of your tables, nor the data they contain. But what you've described is the expected result of joining multiple records - if you have three lines and two payments for one order, and join them on the order ID, you will get six records in the output. Visual Representation of SQL Joins[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
We can't see the structure of your tables, nor the data they contain. But what you've described is the expected result of joining multiple records - if you have three lines and two payments for one order, and join them on the order ID, you will get six records in the output. Visual Representation of SQL Joins[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You can't. You can create a small reproduction of your tables using sample data as a SQL Fiddle[^], and post the link to that.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
A real image would be rather weird :D What's the structure, and some example data. If you can provide those, then we can "try" your code with your examples and play a bit with it. Without that, we'd have to guess.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
We can't see the structure of your tables, nor the data they contain. But what you've described is the expected result of joining multiple records - if you have three lines and two payments for one order, and join them on the order ID, you will get six records in the output. Visual Representation of SQL Joins[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
