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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-05 : 08:32:56
|
| Daken writes "Hello out there. Thanks in advance for the question. I have a pretty straightforward question, but I can't seem to figure out the correct, and most efficient, way to handle it. I am using MSFT SQL Server 2000 and Windows XP Pro SP2.If I have two tables, A and B as follows:A1 $1002 $1003 $4004 $200B1 Frank2 Tom3 Jan5 Alex6 JulieI know how to join the tables as in:Select * from A left join B on B.COL001 = A.COL001And I will have the first three rows returned as planned. Now what I want is to have the last two rows from the B table returned. In other words, return everything from the Table B that does not match on B.COL001 = A.COL001. Does that make sense? Essentially, it is the left over records after a left or inner join.Thanks in advance. I greatly appreciate it.Daken" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-05 : 09:33:44
|
just write the opposite of your statement:Select B.* from A B left join B A on B.COL001 = A.COL001look at the results. the LEFT JOIN returns NULL values in the column A.COL001 if there is no match in the outer table. so you just add a WHERE clause and you have your answer:Select B.* from B left join A on B.COL001 = A.COL001where A.COL001 IS NULL- Jeff |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-05 : 13:54:18
|
| Or use a full join and filterSelect * from A full join B on B.COL001 = A.COL001--where A.COL001 is null--or B.COL001 is nullthis can be useful in cases where you want to find records from either table that do not matchrockmoose |
|
|
|
VIG
Yak Posting Veteran
86 Posts |
Posted - 2004-11-05 : 15:45:13
|
| or use a RIGHT JOINSelect B.* from Aright join B on B.COL001 = A.COL001where A.COL001 IS NULL |
|
|
|
|
|
|
How can I find leftover records?