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 |
|
akamole
Starting Member
17 Posts |
 Posted - 2014-03-14 : 10:32:31
|
I want to select Property names or owner id from table A where there is no match between owner ID in table A and B , for example Property 3 in red. In table A only one person can be listed as owner of property. In table B many people can be listed as owner of the same property.The way i script will return property 1 twice (as no match) because the owner ID from table A '123456' mismatch twice with owner ID from table B'111111' and '222222'. I dont want Property 1 to be selected at all because the owner '123456' is listed as one of the owners in Table BHow do i solve this? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-14 : 11:13:22
|
| [code]SELECT * FROM TableBWHERE propertyname NOT IN( SELECT DISTINCT b.propertyname FROM TableB b INNER JOIN TableA a ON a.propertyname = b.propertyname AND a.ownerid = b.ownerid));[/code] |
 |
|
|
akamole
Starting Member
17 Posts |
Posted - 2014-03-17 : 10:54:12
|
| Thankyou! |
|
|
|
|
|
|
Select where no match