Altaf Patni
asked on
Sql Query join multiple table and distinct records
Hi
I am trying to get distinct and summarized record from one table and from other tables i want without summarized data,
please assist me what am i doing wrong
so far i wrote a query,
I am trying to get distinct and summarized record from one table and from other tables i want without summarized data,
please assist me what am i doing wrong
so far i wrote a query,
SELECT DISTINCT
dbo.TPTCrsngLRDtld.MemoNo, dbo.TPTCrsngLRDtld.M_Date, SUM(dbo.TPTCrsngLRDtld.Vasuli) AS TotalVasuli, SUM(dbo.TPTCrsngLRDtld.Crossing)
AS TotalCrosing, SUM(dbo.TPTCrsngLRDtld.Balance) AS TotalBalance, dbo.TPTCrsngLRDtld.Cros_No AS RcptNo, dbo.TPTCrsngLRDtld.Amt_Rcvd,
dbo.TPTCrsngLRDtld.Amt_Paid, dbo.TPTCrsngLRDtld.Delivery_at, dbo.TPTCrsngLRDtld.Destination
FROM dbo.TPTCrsngLRDtld INNER JOIN
dbo.LCM_DELV_Crosing ON dbo.TPTCrsngLRDtld.MemoNo = dbo.LCM_DELV_Crosing.LCMNo
UNION
SELECT 0 AS MemoNo, Receipt_Tport.Rcpt_Date, 0 AS TotalVasuli, 0 As TotalCrosing, 0 AS TotalBalance, Receipt_Tport.RcptNo, Receipt_Tport.Amt_Rcvd,
0 AS Amt_Paid, Receipt_Tport.G_Name, 'N/A' As Destination
FROM Receipt_Tport
UNION
SELECT 0 AS MemoNo, Pymt_Tport_DELV.Rcpt_Date, 0 AS TotalVasuli, 0 AS TotalCrosing, 0 AS TotalBalance, Pymt_Tport_DELV.RcptNo, 0 AS Amt_Rcvd,
Pymt_Tport_DELV.Amt_Rcvd AS Amt_Paid, Pymt_Tport_DELV.G_Name, 'N/A' AS Destination
FROM Pymt_Tport_DELV
GROUP BY dbo.TPTCrsngLRDtld.MemoNo, dbo.TPTCrsngLRDtld.Amt_Rcvd, dbo.TPTCrsngLRDtld.Amt_Paid,dbo.TPTCrsngLRDtld.Delivery_at,
dbo.TPTCrsngLRDtld.Destination, dbo.TPTCrsngLRDtld.Cros_No, dbo.TPTCrsngLRDtld.M_Date,
dbo.Receipt_Tport.MemoNo, dbo.Receipt_Tport.Amt_Rcvd, dbo.Receipt_Tport.Amt_Paid,dbo.Receipt_Tport.Delivery_at,
dbo.Receipt_Tport.Destination, dbo.Receipt_Tport.Cros_No, dbo.Receipt_Tport.M_Date
ASKER
No i dont want to delete any record, i just want to display record for report purpose
Please give us a data mockup, both data in tables and expected output in the report, of what you're trying to pull off here. Based on the question and above comment it's not clear.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Shaun
"/* Are these fields needed? They are not included in the SELECT clause. */
dbo.TPTCrsngLRDtld.Cros_No , dbo.TPTCrsngLRDtld.M_Date, "
yes this fields are needed and both fields are in first select statement (please check )
"dbo.TPTCrsngLRDtld.Cros_N o AS RcptNo" and "dbo.TPTCrsngLRDtld.M_Date "
/* Is this GROUP BY needed? */
I don't know,
"/* Are these fields needed? They are not included in the SELECT clause. */
dbo.TPTCrsngLRDtld.Cros_No
yes this fields are needed and both fields are in first select statement (please check )
"dbo.TPTCrsngLRDtld.Cros_N
/* Is this GROUP BY needed? */
I don't know,
If you run the individual SELECT statements I posted, do you get the results you are expecting? For the second SELECT statement, ignore the GROUP BY when you first run it. If the results are not what you expect, run it with the GROUP BY.
As Jim mentioned earlier, it will help if you can provide sample data for each table and what your expected results should be.
As Jim mentioned earlier, it will help if you can provide sample data for each table and what your expected results should be.
ASKER
Thanks Shaun :-)
Open in new window