select parent records that do not have a particular child

[d-42]

Hi,

I've got a many-many relationship between people and locations:

Persons {personid, namefirst, accountid}
Locations {locationid, locationname, accountid}

// many-many join table
PersonLocation {personid, locationid}

I want to find all the people who belong to a particular account, who
are not associated with a particular location.

For example:

ie: If I've got 4 people:

{1, Homer, x}
{2, Marge, x}
{3, Ned, x}
{4, Moe, x}
{5, Bullwinkle, y}

and two locations:

{1, Simpson's Home, x}
{2, Moe's Tavern, x}

and a match table as follows:
Homer has both the home, and the bar
Marge has just the home.
Ned has none.
Moe has just the bar.
Bullwinkle has none.

or records: {1,1}, {1,2}, {2,1},{4,2}

I want to find all the people who belong to account 'x', who are NOT
associated with Moe's Tavern.
i.e. -- I want to return just Marge and Ned.

I came up with this:

@P0 is the accountid I'm interested in.
@P1 is the locationid I'm interested in.

SELECT
[t0].[personid] AS [personid],
[t0].[namefirst] AS [namefirst],
[t0].[accountid] AS [accountid],
[t1].[locationid] AS [locationid]
FROM [dbo].[Persons] AS [t0]
LEFT OUTER JOIN
(SELECT [t2].[personid], [t2].[locationid] FROM [dbo].
[PersonLocation] AS [t2]
WHERE [t2].[locationid]=@P1)
AS [t1] ON [t0].[personid] = [t1].[personid]
WHERE
([t0].[accountid] = @P0) AND
([t1].[locationid] IS NULL)

This appears to work, but is it the best way?

Thanks,
Dave

Thanks,
Dave

[Ed Murphy]

d-42 wrote:

I want to find all the people who belong to a particular account, who
are not associated with a particular location.

[snip]

SELECT
[t0].[personid] AS [personid],
[t0].[namefirst] AS [namefirst],
[t0].[accountid] AS [accountid],
[t1].[locationid] AS [locationid]
FROM [dbo].[Persons] AS [t0]
LEFT OUTER JOIN
(SELECT [t2].[personid], [t2].[locationid] FROM [dbo].
[PersonLocation] AS [t2]
WHERE [t2].[locationid]=@P1)
AS [t1] ON [t0].[personid] = [t1].[personid]
WHERE
([t0].[accountid] = @P0) AND
([t1].[locationid] IS NULL)

This appears to work, but is it the best way?

The following syntax allows you to say what you really mean:

select personid, namefirst
from Persons t0
where accountid = @P0
and not exists (
select *
from PersonLocation t2
where t2.personid = t0.personid
and t2.locationid = @P1
)

[d-42]

On Apr 18, 7:13 am, Ed Murphy <[e-mail]:

d-42 wrote:

I want to find all the people who belong to a particular account, who
are not associated with a particular location.

[snip]

SELECT
[t0].[personid] AS [personid],
[t0].[namefirst] AS [namefirst],
[t0].[accountid] AS [accountid],
[t1].[locationid] AS [locationid]
FROM [dbo].[Persons] AS [t0]
LEFT OUTER JOIN
(SELECT [t2].[personid], [t2].[locationid] FROM [dbo].
[PersonLocation] AS [t2]
WHERE [t2].[locationid]=@P1)
AS [t1] ON [t0].[personid] = [t1].[personid]
WHERE
([t0].[accountid] = @P0) AND
([t1].[locationid] IS NULL)

This appears to work, but is it the best way?

The following syntax allows you to say what you really mean:

select personid, namefirst
from Persons t0
where accountid = @P0
and not exists (
select *
from PersonLocation t2
where t2.personid = t0.personid
and t2.locationid = @P1
)

Thank you, yes, that is much more succint.

Unfortunately I can't seem to express this in linq (no 'exists'
keyword), but it has led me to a better way of expressing it in linq
than I was:

var q2 = from p in Persons
where p.account == accid
where !(from x in PersonDistLocation
where x.DistLocationID == distlocationid
select x.PersonID).Contains(p.PersonID)
select p;

which is much more readable than the linq I had, and its analogous to
what you've given me.
(linq is still using an outer join though, and I'm hoping the
performance is equivalent.)

Thanks,
Dave