On Apr 18, 7:13 am, Ed Murphy <emurph...@[EMAIL PROTECTED]
> 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]=@[EMAIL PROTECTED]
)
> > AS [t1] ON [t0].[personid] = [t1].[personid]
> > WHERE
> > ([t0].[accountid] = @[EMAIL PROTECTED]
) 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 = @[EMAIL PROTECTED]
> and not exists (
> select *
> from PersonLocation t2
> where t2.personid = t0.personid
> and t2.locationid = @[EMAIL PROTECTED]
> )
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
|
