Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

#1: select parent records that do not have a particular child

Posted on 2008-04-18 14:55:13 by 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

Report this message

#2: Re: select parent records that do not have a particular child

Posted on 2008-04-18 16:13:36 by 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
)

Report this message

#3: Re: select parent records that do not have a particular child

Posted on 2008-04-19 00:52:39 by d-42

On Apr 18, 7:13 am, Ed Murphy <emurph...@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

Report this message