Post reply

sql query question

  • November 22, 2009 at 7:24 pm

    #71886

    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

    #1082785

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply