get opposite records from a join

[ktucci]

how would i get the opposite of this query...all records in temp_b_sd_02 that are not in the join

if i switch the = to <> in the join it doesnt run or runs extrememly slow

or create a flag field in temp_b_sd_02 and update would work if i could update it from the results of this query

not sure what the best method is or how to do it

----------------------------------------------------------

SELECT DISTINCT
temp_b_sd_02.sd_line_id, temp_b_sd_02.sd_item_info,
temp_b_sd_02.sd_rate_type, temp_a_sd_02.resale_desc,
temp_b_sd_02.type, temp_a_sd_02.resale_code,
temp_a_sd_02.resale_state, temp_b_sd_02.sd_bill_amt
FROM dbo.temp_a_sd_02 INNER JOIN
dbo.temp_b_sd_02 ON
dbo.temp_a_sd_02.type = dbo.temp_b_sd_02.type AND
dbo.temp_a_sd_02.sd_line_id = dbo.temp_b_sd_02.sd_line_id

 

[tlbroadbent]

If I understand correctly, you want to display rows from temp_b_sd_02 that are not in the INNER JOIN. Another way to say that is select all rows from temp_b_sd_02 that are not in the temp_a_sd_02. In that case, use an &quot;OUTER JOIN&quot; and test a field on temp_a_sd_02 for NULL. This query will select all records on b that are not on a.

SELECT
b.sd_line_id, b.sd_item_info,
b.sd_rate_type, b.resale_desc,
b.type, b.resale_code,
b.sd_bill_amt
FROM dbo.temp_a_sd_02 a RIGHT OUTER JOIN
dbo.temp_b_sd_02 b ON
a.type = b.type AND
a.sd_line_id = b.sd_line_id
WHERE a.resale_state is null

The word OUTER is not required. I gave each table an alias to reduce length of query and make the query more readable. I also removed &quot;resale_state&quot; from the selection list as you only want to see records from temp_b_sd_02. Terry

http://members.home.net/tlbroadbent/index.htm

 

[ktucci]

thanks a million...it worked great

keith