Cross Join Query Help Required

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

General SQL Server Forums

New to SQL Server Programming

Cross Join Query Help Required

Author

Topic

joshtheflame
Starting Member

5 Posts

Posted - 2011-01-08 : 14:06:32

i have following table structure
HUB_DETAILS (Master)

Branch_ID
Branch_Name
VTRCheckList (Master)

CLid
CLName
VTRCheckListDetails (Detail)

CLid
Branch_ID
VTRValue
vtrRespDate

I Want the data to be populated with all the branches exists in Hub_Details and show the checklist names alongwith the branches with the sum of vtrvalue field.


SELECT     VTRCheckList.CLName, Hub_Details.BranchName,         sum(cast(VTRCheckListDetails.VtrValue as int)) as 'Total'  
  FROM         VTRCheckListDetails 
          INNER JOIN    VTRCheckList ON VTRCheckListDetails.CLid = VTRCheckList.CLid CROSS JOIN
                Hub_Details
 where Convert(date,VTRCheckListDetails.vtrRespDate, 105) >=      convert(date,'01-01-2011',105) and Convert(date, VTRCheckListDetails.vtrRespDate, 105) <= convert(date,'30-01-2011',105)                    
   GROUP BY VTRCheckList.CLName, Hub_Details.BranchName

that shows e.g. total vtrvalues by CheckListName and BranchName. If no inputs were made in CheckListName in any Branch then we want to see a row with a zero, rather than just not showing a row.

This query is working great but when I put the where clause to check all the branches position in between dates it shows only the branch which meets the criteria. I want to show all the branches no matter which criteria i put it in. If i remove "and s.branchid = p.branchID" it comes with all branches but the vtrvalue repeats their values in all branches.

Any help?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 14:38:40

I think you need the aggregate to be a subquery for it return all results that you need. Not sure, but something like this:


SELECT HubDetails.*, sub.*
FROM
(  SELECT	VTRCheckList.CLName
		,Hub_Details.BranchName
		,sum(cast(VTRCheckListDetails.VtrValue as int)) as Total
   FROM         VTRCheckListDetails 
          INNER JOIN    VTRCheckList ON VTRCheckListDetails.CLid = VTRCheckList.CLid
   where Convert(date,VTRCheckListDetails.vtrRespDate, 105) >=      convert(date,'01-01-2011',105) and Convert(date, VTRCheckListDetails.vtrRespDate, 105) <= convert(date,'30-01-2011',105)                    
   GROUP BY VTRCheckList.CLName, Hub_Details.BranchName  

) sub CROSS JOIN Hub_Details

You could also perhaps do a left join from Hubdetails to do it


SELECT HubDetails.BranchName
      ,sub.Total
FROM HubDetails LEFT JOIN
(  SELECT	VTRCheckList.CLName
		,Hub_Details.BranchID
		,sum(cast(VTRCheckListDetails.VtrValue as int)) as Total
   FROM         VTRCheckListDetails 
          INNER JOIN    VTRCheckList ON VTRCheckListDetails.CLid = VTRCheckList.CLid
   where Convert(date,VTRCheckListDetails.vtrRespDate, 105) >=      convert(date,'01-01-2011',105) and Convert(date, VTRCheckListDetails.vtrRespDate, 105) <= convert(date,'30-01-2011',105)                    
   GROUP BY VTRCheckList.CLName, Hub_Details.BranchName  

) sub 
ON HubDetails.BranchID = sub.BranchID

which would return all HubDetails records and only matching subquery records.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 01:14:34

seems like this


SELECT t.Branch_Name,t.CLName,
SUM(vcld.VTRValue) AS 
FROM (SELECT Branch_ID,Branch_Name,CLid,CLName
      FROM HUB_DETAILS hd
      CROSS JOIN VTRCheckList vcl)t
LEFT JOIN VTRCheckListDetails vcld
ON vcld.CLid = t.CLid
AND vcld.Branch_ID = t.Branch_ID
GROUP BY t.Branch_Name,t.CLName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources