CODEWITHSUNDEEP
sqlsql-servert-sql
Sean Long
Sean Long

Reputation: 2301

One to many relationships in T-SQL

I need to figure out how to find a certain group of records using T-SQL and I'm having trouble figuring out how I would need to create the WHERE clause to do this.

I have a SQL 2008 R2 system that I'm working with, and in this database there are a couple of tables. One contains personnel records, and another contains addresses. The addresses relate to the personnel records by a foreign key relationship. So for example, to get a list of all personnel and all of their associated addresses (a single person could have multiple addresses) I could write something like this:

 SELECT id, name FROM personnel p
      INNER JOIN address a
      ON p.id = a.personnelid

However, each address has a column called isprimary, that is either 0 or 1. What I need to do is figure out how to find all personnel who do not have an associated address with isprimary set to 1. Or records that have no primary address.

Currently my thought is to build a temporary table with personnel who have addresses that aren't marked as primary. Then cycle through those and build a subset that have a primary address.

Then subtract the Personnel With Primary table from the results of Personnel With Non-Primary and I should have my list. However, I'm thinking that there has to be a more elegant way of doing this. Any ideas?

Upvotes: 0

Views: 127

Answers (3)

ARA
ARA

Reputation: 1316

This ends up beeing a Left anti semi join pattern and can be written like this:

SELECT id, name FROM personnel p
LEFT OUTER JOIN address a
    ON p.id = a.personnelid
    AND a.isprimary = 1
WHERE a.personnelId IS NULL

It can be interesting to test different ways because query plan are often not the same.

Upvotes: 0

Ravi Trivedi
Ravi Trivedi

Reputation: 2360

SELECT id, name FROM personnel p
INNER JOIN address a
ON p.id = a.personnelid
AND a.isprimary = 0

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7753

Try this, it should get all Personnel rows with no matching primary address:

SELECT *
FROM Personnel p
WHERE NOT EXISTS
   (SELECT * FROM Address a WHERE a.personnelId = p.id AND a.isprimary = 1)

Upvotes: 5

Related Questions