July 10, 2007 at 7:01 am
Hello everyone,i m using SQL Server, i have 2 tables
table 1: Packages
PackageID,
PackageName,
LaneworkingModeCode (FK)
TollTimeSpanCode (FK)
Table 2:TollTimeSpans
TollTimeSpanCode (PK) (1,2,3,4)
FromTime
ToTime
in 1 table, no data for TollTimeSPanCode=4, but it is in Table2 so i want this.
now the problem is in the table TollTimeSpans there is 4 enteries (TollTimeSpanCodes :1,2,3,4), i want to make a query that return the data but the
point is return all records from the TollTimeSpan table, but my this query returne the 1,2,3 codes data not return the 4 code data, as i have no data in the packages table , but i want its returns the
4 code as well from TollTimeSpans table, kindly tell me how i do this.plz help me.
here is my query ,
SELECT dbo.Packages.PackageCode, dbo.Packages.LaneWorkingModeCode, dbo.LaneWorkingModes.WorkingMode, dbo.Packages.TollTimeSpanCode,
dbo.TollTimeSpans.FromTime, dbo.TollTimeSpans.ToTime, dbo.Packages.PackageName, dbo.Packages.TollAmount
FROM dbo.Packages Right Outer JOIN
dbo.LaneWorkingModes ON dbo.Packages.LaneWorkingModeCode = dbo.LaneWorkingModes.LaneWorkingModeCode INNER JOIN
dbo.TollTimeSpans ON dbo.Packages.TollTimeSpanCode = dbo.TollTimeSpans.TollTimeSpanCode
WHERE Packages.TollingVehicleCategoryCode=3 and Packages.LaneWorkingModeCode=1
July 10, 2007 at 2:18 pm
Change your INNER JOIN to an OUTER JOIN. Inner joins do not return values when one is NULL. Outer joins will return values even if one of the values is NULL.
1 A
2 B
3 NULL
Inner Join will only return:
1 A
2 B
Left Outer Join will return: (everything from left column and where there is no value in right column return NULL)
1 A
2 B
3 NULL
-Bill
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply