Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
 Posted - 2005-01-10 : 11:53:10
|
| Hey all-I need to find all parts that are not listed in the E_PARTS table (system conversion clean up). Which way is better to find unmatched items?I first wrote the query using a sub query.SELECT DISTINCT EO_P_ID FROM E_MASTER WHERE EO_P_ID NOT IN(SELECT DISTINCT P_ID FROM E_PARTS) AND EO_TYPE='A' AND EO_ACCOUNT LIKE '501%';Then I worte it using a left join. It's been a while since I've had my "SQL hat" on so please bare with me.SELECT DISTINCT RTrim([EO_P_ID]) AS BLAFROM E_MASTER LEFT JOIN E_PARTS ON E_MASTER.EO_P_ID = E_PARTS.P_IDWHERE (((RTrim([EO_P_ID])) Is Not Null) AND ((E_PARTS.P_ID) Is Null) AND ((E_MASTER.EO_TYPE)='A') AND ((E_MASTER.EO_ACCOUNT) Like '501%'));Which way is better? More accurate?Thanks,Doug |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-01-10 : 15:15:18
|
| I prefer the LEFT JOIN MethodDid you do a showplan on both?Brett8-) |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-10 : 15:41:25
|
| also --1) get rid of all those parens! generated by EM, right?2) don't do this:RTrim([EO_P_ID])) Is Not Nulluse:[EO_P_ID] Is Not Null3) prefix your columns with the table they are coming from, even if they are all named uniquely amongs the tables involved. It's impossible for us to know which column is coming from which table w/o looking at your schema, and it's a good idea in general. Use nice short alias's on the table names to make this easy to read/write.(Edit: I almost forgot this one)4) I personally prefer LEFT OUTER JOIN -- it is more flexible. - Jeff |
|
|
|
|
|
|
Find Unmatched, Use Sub Query or Join?