November 22, 2009 at 7:24 pm
i have a query where I am joining two tables. I need all ids of table1 match to the table2. There are records where ids dont match. Those that dont match should be matched with a different column in table2.
Ex query
select * from table a
left outer join table b on b.testid = a.testid
and test_code = '20'
For ex if there are total 800 records but there are 780 match in table a that match with the id in table b there are 20 that does not exist in table b for the match but should get them by matching to a different column in table b.
when I dont have a condition for test_code then I get all 800. But when I add the test code (which i should) the rest of the 20 records I need to get by matching with different column in table b.
November 22, 2009 at 7:34 pm
this is an excellent question that trips up a lot of people.
try both of these queries and see the results should be different:
select * from table a
left outer join table b on b.testid = a.testid
and test_code = '20'
select * from table a
left outer join table b on b.testid = a.testid
WHERE test_code = '20'
the difference, if it is not obvious, is that the second query does not use test_code = '20' as part of the JOIN, but instead tests the results of the join with the WHERE.
unless you really had a compound condition for the join, you want to move the part that tests the data to the WHERE clause.
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply