November 20, 2007 at 11:07 pm
hello everyone, i m using SQLServer 2000, here are my tables:
Tolling Vehicle Categories
Tolling Vehicle CategoryCode int
Tolling Vehicle CategoryName varchar(50)
ParentID int
DefaultTollAmount money
Description varchar(50)
Transactions
TransactionID int
ShiftID (FK) int
TollAmount money
ChargedAmount money
ShiftsInformation:
ShiftID int
ShiftStatus
TollAmountCollected
ShiftOpenedDate
ShiftClosedDate
SELECT dbo.TollingVehicleCategories.TollingVehicleCategoryCode, dbo.TollingVehicleCategories.TollingVehicleCategoryName,
COUNT(dbo.Transactions.TransactionID) AS [Total Vehicles], ISNULL(SUM(dbo.Transactions.ChargedAmount), 0) AS [Total Amount]
FROM dbo.Transactions RIGHT OUTER JOIN
dbo.TollingVehicleCategories ON dbo.Transactions.VehicleCategoryCode = dbo.TollingVehicleCategories.TollingVehicleCategoryCode
WHERE (dbo.TollingVehicleCategories.ParentID = 0) AND dbo.[Transactions].[ShiftID]=46
GROUP BY dbo.TollingVehicleCategories.TollingVehicleCategoryName, dbo.TollingVehicleCategories.TollingVehicleCategoryCode,
dbo.TollingVehicleCategories.TollingVehicleCategoryName
here IS my query, now it shows ALL reocrd which IS IN ON the TABLE [TollingVehicleCategories], i want TO show ALL record OF that TABLE so thats
y i m using RIGHT OUTER JOIN , now i want TO make SOME fileters IN the WHERE clause AS FOR example
WHERE (dbo.TollingVehicleCategories.ParentID = 0) AND dbo.TRANSACTIONs.ShiftID =46
now i got the 2 records, AS my TRANSACTION TABLE 2 ids are present FOR Shoft IF 46, but i need my query shows ALL let say 3 records FROM the
TollingVehicleCategories TABLE AND the columns TotalVehicles AND TotalAmount IN my query , they both showed 0 (data) AS no data FOR shiftid 46.
here IS the result my query shows without the filter SHiftid= 46:
TollingVehicleCategoryCode TollingVehicleCategoryName TotalVehicles TotalAmount
1 Car 1 10.0000
6 Wagon 1 40.0000
9 ABC 0 0.0000
AND after adding this IN WHERE clause my query returns:
TollingVehicleCategoryCode TollingVehicleCategoryName TotalVehicles TotalAmount
1 Car 1 10.0000
6 Wagon 1 40.0000
i need my query RETURNS after adding ShiftID =46 IN the WHERE clause:
TollingVehicleCategoryCode TollingVehicleCategoryName TotalVehicles TotalAmount
1 Car 1 10.0000
6 Wagon 1 40.0000
9 ABC 0 0
plz tell me how i do this, TO achieve this i need 2 query OR used CURSOR, IF i need TO write CURSOR FOR this THEN how
i write AS i m new IN cursors. Plz help me AND reply me asap.
Thanx IN Advance.
November 21, 2007 at 6:38 am
Conceptually in SQL the FROM clause is evaluated first and then the results are filtered by the WHERE clause.
Think about what you are doing! Will NULL = 46 ever be true?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply