Prev: sp_prepexec syscomments?
Next: how to create my own primary key which auto-increment
From: d-42 on 18 Apr 2008 08:55
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



From: Ed Murphy on 18 Apr 2008 10:13
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
)
From: d-42 on 18 Apr 2008 18:52
On Apr 18, 7:13 am, Ed Murphy <emurph...(a)socal.rr.com> wrote:
> 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
 | 
Pages: 1
Prev: sp_prepexec syscomments?
Next: how to create my own primary key which auto-increment