Link to home
Start Free TrialLog in
Avatar of Altaf Patni
Altaf PatniFlag for India

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,
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

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Not really sure what you are asking us.  If it's just to delete the SUM values and display the details, then remove the SUM() as alias_name from the SELECT clause, and delete the GROUP BY clause.
SELECT DISTINCT 
	dbo.TPTCrsngLRDtld.MemoNo, dbo.TPTCrsngLRDtld.M_Date, dbo.TPTCrsngLRDtld.Vasul, dbo.TPTCrsngLRDtld.Crossing, 
	dbo.TPTCrsngLRDtld.Balance, dbo.TPTCrsngLRDtld.Cros_No AS RcptNo, dbo.TPTCrsngLRDtld.Amt_Rcvd, 
	dbo.TPTCrsngLRDtld.Amt_Paid, dbo.TPTCrsngLRDtld.Delivery_at, dbo.TPTCrsngLRDtld.Destination
FROM dbo.TPTCrsngLRDtld 
	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

Open in new window

Avatar of Altaf Patni

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
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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_No AS RcptNo" and "dbo.TPTCrsngLRDtld.M_Date"

/* 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.
Thanks Shaun  :-)