Menu
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 JOINs. Is there a way around this, or am I looking for something else entirely?
479
An outer join returns all of the rows that the equivalent inner join would return, plus non-matching rows from one or both tables. In the FROM clause, you can specify left, right, and full outer joins. In the WHERE clause, you can specify left and right outer joins only.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
When you use a Left Outer join without an On or Where clause, there is no difference between the On and Where clause. Both produce the same result as in the following. First we see the result of the left join using neither an On nor a Where clause.
A left outer join is a method of combining tables. The result includes unmatched rows from only the table that is specified before the LEFT OUTER JOIN clause. If you are joining two tables and want the result set to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.
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
194
The predicate condition needs to be in the On clause for the join, not in the where clause. The way Outer joins work, is after the join conditions are analyzed, all the rows from the "outer side" that do not match the inner side are added back in.... But this all happens before the where clause is processed. So if the where clause predicate filters on an attribute from the outer side of an outer join, all those rows will be removed again... (They are all null). Put the predicate in the join condition instead, then it will get evaluated before the missing rows are added back in...
SELECT b.ID, t.RailType, b.CreatedPieces, b.OutsideSource, b.Charged, b.Rejected, b.RejectedToCrop FROM indRailType t LEFT JOIN WO_BreakerRail b ON t.RailCode = b.RailCode And b.Date = @Date If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
