I have two tables. indRailType
contains a list of the names paired with an ID value that I use in other tables to indicate the rail type. WO_BreakerRail
contains a date column and a rail code column that corresponds to the same code in indRailType
and some other data. There's a row in WO_BreakerRail
for any activity on each rail type, for every date. So I could have 3 rows dated for 3/19/2010
, each row indicates a different rail code, and what happened.
When I use the following LEFT OUTER JOIN
, I get a table with all the types of rail, with nulls in the rows where nothing happened on the 19th. Now, this is only working because I only have one date represented in my WO_BreakerRail
table right now, the 19th. When I add more rows with different dates, things will go haywire.
This is my SQL statement, which right now gives me exactly the results I want:
SELECT WO_BreakerRail.ID, indRailType.RailType, WO_BreakerRail.CreatedPieces,
WO_BreakerRail.OutsideSource, WO_BreakerRail.Charged,
WO_BreakerRail.Rejected, WO_BreakerRail.RejectedToCrop
FROM indRailType
LEFT OUTER JOIN WO_BreakerRail
ON indRailType.RailCode = WO_BreakerRail.RailCode
Now, when I add in a WHERE WO_BreakerRail.Date = @Date
clause I lose all the rows in the JOIN
which nothing happened. I don't want that. From reading up, it sounds like a FULL OUTER JOIN
is what I want, but SQL Server Compact Edition doesn't support FULL OUTER JOIN
s. Is there a way around this, or am I looking for something else entirely?
Try:
SELECT WO_BreakerRail.ID, indRailType.RailType, WO_BreakerRail.CreatedPieces,
WO_BreakerRail.OutsideSource, WO_BreakerRail.Charged,
WO_BreakerRail.Rejected, WO_BreakerRail.RejectedToCrop
FROM indRailType
LEFT OUTER JOIN WO_BreakerRail ON indRailType.RailCode = WO_BreakerRail.RailCode
AND WO_BreakerRail.Date = @Date
Thus adding AND WO_BreakerRail.Date = @Date
onto the join