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 |
|
MattKuss
Starting Member
2 Posts |
 Posted - 2002-02-27 : 09:59:56
|
| We have two tables, each with a ssn and course in the table (and other fields as well).I am wanting to retrieve a list of records in TableA that are not in TableB. The way to tell if a record is in a table is only by the ssn and course.So I need every record from TableA in which the ssn and course in that record is not in a records in TableB.I have dug around, but cant seem to find an answer for this. I am able to get the number of records that meet that criteria by finding the total number of records in Table A, and subtracting the records in Table B. Problem is that both the ssn and the course, independently, appear more than once in each table, so neither by itself is unique. It is only the combination of ssn/course that is unique.Any help would be appreciated.Matt |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-27 : 10:04:08
|
| SELECT A.* FROM A LEFT JOIN B ON (A.SSN=B.SSN AND A.Course=B.Course)WHERE B.SSN Is Null AND B.Course Is NullThe LEFT JOIN will return all rows from A, and the WHERE clause will return only those rows that don't match in B (if they don't match, the B columns will be Null). |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-27 : 10:04:34
|
| select *from tablea a left outer join tableb b on a.ssn = b.ssn and a.course = b.coursewhere b.ssn is null==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-27 : 10:05:13
|
| Must type faster.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
MattKuss
Starting Member
2 Posts |
Posted - 2002-02-27 : 12:14:42
|
| Thanks Rob and the slighty slower typing nr. I appreciate it.Matt |
|
|
|
|
|
|
Finding unmatched records on two fields